none
Userforms COPY and Past RRS feed

  • Question

  • File with all in is in they end of this question

    Main Password for All function in this Document is "Dart"

    In a file i have 28 sheets each sheet's are look align.

    i have make 2 userforms, one for copy and one paste.

    on both userform's there are 1 to 28 checkbox

    on userform1 they mark Range C6:AD46 for copy

    on userform2 they mark Range C6:AD46 for past

    The Point in this function is when program-File manager are comming with changes in main file and user's have type Game result's then they should could copy em from old file and put em into the new Updated File. so old file the user have to rename it to a Name different than Updated file. but that's minor problem. the problem i got is to get userform's to work

    The Copy code i got is this

    Private Sub CheckBox10_Click()
    If CheckBox1.Value = True Then

    'ActiveSheet.Unprotect Password:="Dart"
    Sheets("Kamp 10").Select
         Range("C6:AD46").Select
         'Application.CutCopyMode = False
         Selection.Copy

    'ActiveSheet.Protect Password:="Dart"
    End If

    End Sub

    And the Past Code i got is this.

    Private Sub CheckBox29_Click()
    If CheckBox29.Value = True Then

    Sheets("Kamp 1").Unprotect Password:="Dart"
        Sheets("Kamp 1").Select
        Range("C6:AD46").Select
       Sheets("Kamp 1").Select
    Sheets("Kamp 1").Paste
    Sheets("Kamp 1").Protect Password:="Dart"
    End If
    End Sub

    The function i seek is when every markt checkbox on userform1 is to COPY ALL markt Area

    and the same for Userform2 But Past function

    Hope that was enough Info file is to download here

    https://www.dropbox.com/s/v04r4rhafk3wk5r/Kopi-S%C3%A6t-ind-Test-1.xlsm?dl=0

    • Moved by Dave PatrickMVP Sunday, January 4, 2015 5:00 PM
    • Moved by Carl Cai Monday, January 5, 2015 2:15 AM more related
    Sunday, January 4, 2015 4:48 PM

Answers

  • Sorry But have first seen Your Message Now

    It seem's to be that ive have made it by my self so thanks anyway for giving me a push in the right direction..

    Thanks Again from Denmark.

    • Marked as answer by Henrik-1 Tuesday, January 13, 2015 7:24 PM
    Tuesday, January 13, 2015 7:24 PM

All replies

  • This forum supports the Windows Forms libraries in the .NET Framework. You appear to be using VBA in Excel. You should post in one of the following forums:

    Sunday, January 4, 2015 5:19 PM
  • Hi Henrik,

    According to your description, I don’t understand clearly about your requirement. Do you want to copy worksheets’ range (the checked checkbox in userform1) and parse to other worksheets (the checked checkbox in userform2)?

    If so, you could iterate Checkbox controls in UserForm and copy corresponding worksheet’s range. The CheckBox controls’ name could be the worksheet name.

    Then add a button in UserForm2, in the click event iterate UserForm1’s checkbox and do the copy and parse.

    To copy and parse the range, you could specify a worksheet (do not need active that worksheet) and there is the Destination parameter that you could use to specify destination worksheet’s range.

    Worksheets("Sheet1").Range("A1:D4").Copy 

    # Range.Copy Method (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx

    About iterate the checkbox controls, please refer to:

    Dim c As Control
      For Each c In UserForm1.Controls
        If TypeName(c) = "CheckBox" Then
        
         MsgBox c.Name & c.Value
         
        End If
        Next c

    On the other hand, to modify the file name, I suggest that you could use SaveAs method.

    # Workbook.SaveAs Method (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff841185(v=office.15).aspx

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, January 6, 2015 8:51 AM
    Moderator
  • According to your description, I don’t understand clearly about your requirement. Do you want to copy worksheets’ range (the checked checkbox in userform1) and parse to other worksheets (the checked checkbox in userform2)?

    yes that is what i want but worksheet's are 2 diffentfile But look the same

    Old file name is "Dart-Game-Old" copy userform's 1 is to be markt wich sheet's there have to been Copy and Parse in "Dart-Game-New"

    new file name is"Dart-Game-New" Pars userform's 2 is to be Markt wich sheet's there have to been Parse in from "Dart-Game-Old"

    And Both File is open....

    i have try it with checkbox1 both copy and parse ,and it seem's to Work BUT BUT when i first mark 1 and then the NeXT number, then the copy function delete the first one and remember the NeXT and so on

    Tuesday, January 6, 2015 7:33 PM
  • Hi Henrik,

    I suggest that you should do Copy and Paste action in a UserForm instead of multiple UserForm. (Checkbox are in a UserForm)

    You could refer to this code below to open another file and paste data.

    Dim strlocation As String
     Dim oxl As Excel.Application
     Dim oBook As Workbook
     Dim oSheet As Worksheet
     strlocation = "[path]\MyExcel.xlsx"
     Set oxl = CreateObject("Excel.Application")
     Set oBook = oxl.Workbooks.Open(strlocation)
     Set oSheet = oBook.Worksheets("Sheet3")
     Range("B1:C2").Copy
     oSheet.Range("B2:C3").Select
     oBook.ActiveSheet.Paste
    oBook.Save
    Set oSheet = Nothing
    Set oBook = Nothing
    oxl.Quit
    Set oxl = Nothing
    MsgBox "Success"

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 7, 2015 2:04 AM
    Moderator
  • I got some trouble in the Code My Sheet's Are Protect with Code "Dart"

    i have try to put to string into your code But it still Say Cell's or Project are Protected......

    Youre Regards from Henrik

    Wednesday, January 7, 2015 10:12 PM
  • Hi Henrik,

    My code is just the simple sample, there isn’t the protect logic.

    oSheet.Unprotect Password:="Dart"
     Range("B1:C2").Copy
     oSheet.Range("B1:C2").Select
     oBook.ActiveSheet.Paste
     oSheet.Protect Password:="Dart"

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 8, 2015 1:48 AM
    Moderator
  • I Have now try your option Starain But get same error
    I now think the trouple might be some cell there are Lock

    All sheet's are protected with Same Password....

    Thanks For your options...

    I have recorded this Macro copy the entire window from dart-1 and paste in dart-2
    the copy and paste functions as it should but Does not formulas and lookup FunctionOne over to the new file (dart-2)

    Code is "

    Sub COMEON()
    '
    ' COMEON Makro
    '

    '
        ActiveWindow.DisplayHeadings = True
        Cells.Select
        Selection.Copy
        Range("F6:N6").Select
        Windows("Dart-2.xlsm").Activate
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Range("F6:N6").Select
        Windows("Dart-1.xlsm").Activate
        Range("F6:N6").Select
    End Sub

    • Edited by Henrik-1 Sunday, January 11, 2015 2:40 PM More info
    Friday, January 9, 2015 10:08 PM
  • Hi Henrik,

    You may share the files on the OneDrive.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 12, 2015 1:25 AM
    Moderator
  • Sorry But have first seen Your Message Now

    It seem's to be that ive have made it by my self so thanks anyway for giving me a push in the right direction..

    Thanks Again from Denmark.

    • Marked as answer by Henrik-1 Tuesday, January 13, 2015 7:24 PM
    Tuesday, January 13, 2015 7:24 PM