Copy from one workbook and paste into another

Question

I have written the following code and continually see pastespecial method of class has failed. I have tried to overcome this issue, but nothing seems to work. I am trying to copy an entire sheet from one workbook, and paste it into another:

Set x = Workbooks.Open(" path to copying book ") Workbooks.Open(" path to copying book ").Activate Range("A1").Select 'Cells.Select Selection.Copy Set y = Workbooks.Open("path to pasting book") Workbooks.Open("Path to pasting book").Activate  With y     Sheets("sheetname").Cells.Select     Range("A1").PasteSpecial     'Sheets("sheetname").PasteSpecial     .Close End With  With x     .Close End With 

Answer 1

This should do it, let me know if you have trouble with it:

Sub foo() Dim x As Workbook Dim y As Workbook  '## Open both workbooks first: Set x = Workbooks.Open(" path to copying book ") Set y = Workbooks.Open(" path to destination book ")  'Now, copy what you want from x: x.Sheets("name of copying sheet").Range("A1").Copy  'Now, paste to y worksheet: y.Sheets("sheetname").Range("A1").PasteSpecial  'Close x: x.Close  End Sub 

Alternatively, you could just:

Sub foo2() Dim x As Workbook Dim y As Workbook  '## Open both workbooks first: Set x = Workbooks.Open(" path to copying book ") Set y = Workbooks.Open(" path to destination book ")  'Now, transfer values from x to y: y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1")   'Close x: x.Close  End Sub 

To extend this to the entire sheet:

With x.Sheets("name of copying sheet").UsedRange     'Now, paste to y worksheet:     y.Sheets("sheet name").Range("A1").Resize( _         .Rows.Count, .Columns.Count) = .Value End With 

And yet another way, store the value as a variable and write the variable to the destination:

Sub foo3() Dim x As Workbook Dim y As Workbook Dim vals as Variant  '## Open both workbooks first: Set x = Workbooks.Open(" path to copying book ") Set y = Workbooks.Open(" path to destination book ")  'Store the value in a variable: vals = x.Sheets("name of sheet").Range("A1").Value  'Use the variable to assign a value to the other file/sheet: y.Sheets("sheetname").Range("A1").Value = vals   'Close x: x.Close  End Sub 

The last method above is usually the fastest for most applications, but do note that for very large datasets (100k rows) it's observed that the Clipboard actually outperforms the array dump:

Copy/PasteSpecial vs Range.Value = Range.Value

That said, there are other considerations than just speed, and it may be the case that the performance hit on a large dataset is worth the tradeoff, to avoid interacting with the Clipboard.

Answer 2

You copied using Cells.
If so, no need to PasteSpecial since you are copying data at exactly the same format.
Here's your code with some fixes.

Dim x As Workbook, y As Workbook Dim ws1 As Worksheet, ws2 As Worksheet  Set x = Workbooks.Open("path to copying book") Set y = Workbooks.Open("path to pasting book")  Set ws1 = x.Sheets("Sheet you want to copy from") Set ws2 = y.Sheets("Sheet you want to copy to")  ws1.Cells.Copy ws2.cells y.Close True x.Close False 

If however you really want to paste special, use a dynamic Range("Address") to copy from.
Like this:

ws1.Range("Address").Copy: ws2.Range("A1").PasteSpecial xlPasteValues y.Close True x.Close False 

Take note of the : colon after the .Copy which is a Statement Separating character.
Using Object.PasteSpecial requires to be executed in a new line.
Hope this gets you going.