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 work book, 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
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.
If however you really want to paste special, use a dynamic Range("Address") to copy from.
Like this:
Take note of the
:
colon after the.Copy
which is aStatement 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:
Alternatively, you could just:
To extend this to the entire sheet:
And yet another way, store the value as a variable and write the variable to the destination:
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.