Copy data from another Excel file with VBA macro

Question

I would like to open a workbook and copy from the opened workbooks sheet to my worbook sheet like that:

Private Sub Workbook_Open()
    Dim openedFile As String
    Dim sourcebook As Workbook
    openedFile = Application.GetOpenFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm") 'Source book opening

    Set sourcebook = Workbooks.Open(openedFile)

    Application.CutCopyMode = True
    sourcebook.Worksheets("source_sheet").Range("A1:L100").Copy
    ThisWorkbook.Worksheets("dest_sgheet").Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Call sourcebook.Close(False)
End Sub

I got this error message after I runing this code:

Run-time error '1004": PasteSpecial method of Range class failed

It's occour at this line:

ThisWorkbook.Worksheets("dest_sheet").Range("A1").PasteSpecial Paste:=xlPasteValues

naiminp naiminp · Accepted Answer · 2016-05-24T14:11:13

try activating the workbook first ("This workbook") and then activating the sheet where you'd like to paste the data. The error tends to occur because either the range is not visible nor in existence.

Copy data from another Excel file with VBA macro

2 Answers