Copy data from current workbook and paste it to another open workbook with userform list

Question

I am wanting to have a button open a userform with a list of all open Workbooks. The user selects the workbook they want and the code copies data from a fixed range in the current workbook and pastes it into a fixed range in the user selected workbook.

While searching around I found this code, that works similarly but copies from the selected workbook and pastes into the current one.

Option Explicit
Const PSWD = "atari"

Private Sub CancelButton_Click()
    Unload Me
End Sub
Private Sub CopyPasteButton_Click()

    ActiveSheet.Unprotect Password:=PSWD
    'This code will be executed when the "Copy" button is clicked on the userform.
    Dim wsData As Worksheet
    Dim rCopy As Range
    Dim CopyRw As Long

    Set wsData = ThisWorkbook.Sheets("SALES Details")

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = True

        With wsData
            .Unprotect PSWD
            CopyRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        End With

        On Error GoTo exit_err

        With Workbooks(Me.ListBox1.Value).Sheets("Master Sheet")
            Set rCopy = .Cells(10, 1).CurrentRegion
            Set rCopy = rCopy.Offset(1, 0).Resize(rCopy.Rows.Count - 1, 40)
            rCopy.Copy ThisWorkbook.Sheets("SALES Details").Cells(CopyRw, 1)
        End With

        Unload Me

exit_err:
        wsData.Protect Password:=PSWD
        .DisplayAlerts = True
        .ScreenUpdating = True
        .CutCopyMode = False
    End With
End Sub

Private Sub UserForm_Activate()

'Populate list box with names of open workbooks, excluding main workbook.

    Dim wb As Workbook

    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then ListBox1.AddItem wb.Name
    Next wb

End Sub

This code works great, for what it does. I have been trying to edit it without luck. How can I edit this to reverse the direction and have it copy from a fixed range in the current sheet (A50:J57) to a fixed range on the user selected sheet (A4:J11)?

Fratyx Fratyx · Accepted Answer · 2014-12-04T12:26:32

I think this should work. Of course you have to adapt the sheet names in code.

Private Sub CopyPasteButton_Click()
    Dim mySheet As Worksheet, otherSheet As Worksheet

    On Error GoTo exit_err

    Application.DisplayAlerts = False

    Set mySheet = ThisWorkbook.Sheets("SheetXYZ")
    Set otherSheet = Workbooks(Me.ListBox1.Value).Sheets("SheetABC")

    mySheet.Range("A50:J57").Copy Destination:=otherSheet.Range("A4:J11")

exit_err:
    Application.DisplayAlerts = True
End Sub

UPDATE

For copying the values and not the formulas of the range use this code instead of the Copy function:

mySheet.Range("A50:J57").Copy 
otherSheet.Range("A4:J11").PasteSpecial xlPasteValuesAndNumberFormats

For further options of the PasteSpecial function see the documentation.

Copy data from current workbook and paste it to another open workbook with userform list

1 Answers