View Full Version : VBA PasteSpecial error
malleshg24
08-15-2019, 02:03 AM
Hi Team,
I need your help in understanding paste special , why it throws error when we write in a single line.
This throws copile error at xlPasteValues
Sheet1.Range("A1").CurrentRegion.Copy Sheet2.Range("A1").PasteSpecial xlPasteValues
This works
Sheet1.Range("A1").CurrentRegion.Copy
Sheet2.Range("A1").PasteSpecial xlPasteValues
Regards,
mg
Paul_Hossler
08-15-2019, 08:05 AM
I think the easy answer is "Because that's the way it is"
I'm guessing here about how the internals of Excel work, but I think that
1. .Copy puts the input Range on the Clipboard, and then Pastes (plain ol' Paste) it to the Output Range if the second parameter is supplied
2. .PasteSpecial assumes that there is something on the Clipboard already and processes it to make it "Special" before putting the results in the Output Range
Did I mention that this is just a guess?
Fluff
08-15-2019, 09:00 AM
My understanding is that
.Copy Destination:= bypasses the clipboard therefore there is nothing to paste.
So you have to do it as two separate opertions
Artik
08-15-2019, 09:34 AM
Another and faster solution is to copy the range without using the clipboard:
Dim rngS As Range
Set rngS = Sheet1.Range("A1").CurrentRegion
With rngS
Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End WithThis method requires the size of the target range, not just the upper left corner of the range as with PasteSpecial.
But it also has its advantages.
Eg CurrentRegion is the range A1: Z20. In the target range, we only need the first two columns
Dim rngS As Range
Set rngS = Sheet1.Range("A1").CurrentRegion
With rngS
Sheet2.Range("A1").Resize(.Rows.Count, 2).Value = .Value
End With
Artik
malleshg24
08-15-2019, 10:34 AM
Hi Paul,Fluff and Artik:thumb,
Thank you all for the information and clearing my doubt.:thumb
Regards,
mg.
Fluff
08-15-2019, 10:43 AM
You're welcome & thanks for the feedback
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.