How to copy data from macro enabled Excel workbook to another Excel file

Question

I am trying to copy data from macro enabled Excel sheet to simple Excel file. But following code is throwing error like object not found.

I am writing VBA code in sheet having one more macro for some other functionality.

Private Sub CommandButton2_Click() 
    Workbooks("JHG raw data.xlsm").Worksheets("Summary").Range("A1:D300").Copy_
    Workbooks("JHG summary.xlsx").Worksheets("Data").Range ("A1")
End Sub

Are both workbooks open in the same Excel instance when you run that code? — Pᴇʜ
Yes both workbooks are opened in the same excel instance when i run the above code. — shailza chaudhary
maybe just a typo but .Copy_ must have a space before the underscore ).Copy _ — Pᴇʜ

Peicong Chen Peicong Chen · Accepted Answer · 2019-05-13T15:32:35

Put Destination:= after Copy

Workbooks("JHG raw data.xlsm").Worksheets("Summary").Range("A1:D300").Copy _
        Destination:=Workbooks("JHG summary.xlsx").Worksheets("Data").Range ("A1")

How to copy data from macro enabled Excel workbook to another Excel file

1 Answers