Open Separate File, Copy Data and Paste Macro

**boohah** · 07-31-2010, 07:43 AM

Hi

I have vba enable spreadsheet open C:\Workbook1.xlsm at "Sheet 1". I would like to write a macro for this file that open a second file, C:\Data.xlsx and copies "Sheet 2" A1:A20 and pastes the values only into Workbook 1, Sheet 1 A1:A20. I then need this macro to Close C:\Data.xlsx without saving.

Any help would be greatly appreciated.

Doug

**pike** · 07-31-2010, 08:27 AM

Hi boohah
something like

    Workbooks.Open Filename:="C:\Data.xlsx"
    Workbook("Data.xlsx").Sheets("Sheet2").Range("A1:D20").Copy Destination:=Workbooks("Workbook1.xlsm").Sheets("Sheet 1").Range("A1")
    Workbook("Data.xlsx").Close Saved = True

**boohah** · 07-31-2010, 08:39 AM

Hi Pike

That code is close but it doesnt paste the values only and I get a compile error when I run it saying that the sub or function is not defined.

**pike** · 07-31-2010, 02:04 PM

Hi boohah

Sub xtest()
Workbooks.Open Filename:="C:\Data.xlsx"
    Workbooks("Data.xlsx").Sheets("Sheet2").Range("A1:D20").Copy (Workbooks("Workbook1.xlsm").Sheets("Sheet2").Range("A1"))
    Workbooks("Data.xlsx").Close Saved = True
End Sub