Copying Contents of a Workbook to another Workbook

Question

I want to copy the content of a worksheet of a workbook to another workbook.

This code only opens both workbooks, not copying and pasting values.

Sub btnClickMev2()
Dim x As Workbook
Dim y As Workbook

'Open Workbooks
Set x = Workbooks.Open("H:\Desktop\test.xls")
Set y = Workbooks.Open("H:\Desktop\Book1v3.xlsm")

'Copy from x Workbook
x.Sheets("Query2").Range("A1:T1036").Copy
'Paste to Y Workbook
y.Sheets("Sheet2").Range("A1:T1036").PasteSpecial
'Close x:
x.Close
End Sub

You might have to turn off `Application.CutCopyMode` before and/or after the paste — Marcucciboy2, Sep 10 '18 at 14:09

score 3 · Accepted Answer · answered Sep 10 '18 at 14:20

3

Hope this helps, found on this Thread and rewritten for your code:

Sub foo3()
Dim x As Workbook
Dim y As Workbook
Dim vals as Variant

'## Open both workbooks first:
Set x = Workbooks.Open("H:\Desktop\test.xls")
Set y = Workbooks.Open("H:\Desktop\Book1v3.xlsm")

'Store the value in a variable:
vals = x.Sheets("Query2").Range("A1:T1036").Value

'Use the variable to assign a value to the other file/sheet:
y.Sheets("Sheet2").Range("A1:T1036").Value = vals 

'Close x:
x.Close

End Sub

answered Sep 10 '18 at 14:20

Doog_Dooger

127
8

1

This is how I would go about it, though I'd probably skip the extra variant variable and go straight in to `y.Sheets("Sheet2").Range("A1:T1036").Value = x.Sheets("Query2").Range("A1:T1036").Value` none-the-less, a nice clean approach +1 – Glitch_Doctor Sep 10 '18 at 14:26

Copying Contents of a Workbook to another Workbook

1 Answers1