VBA PasteSpecial error

[malleshg24]

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]

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]

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]

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]

Hi Paul,Fluff and Artik:thumb,

Thank you all for the information and clearing my doubt.:thumb

Regards,
mg.

[Fluff]

You're welcome & thanks for the feedback