Copy from one workbook and paste into another
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.
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.