VBA PasteSpecial error

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

Code:

---

Sheet1.Range("A1").CurrentRegion.Copy Sheet2.Range("A1").PasteSpecial xlPasteValues

---

This works

Code:

---

Sheet1.Range("A1").CurrentRegion.Copy
Sheet2.Range("A1").PasteSpecial xlPasteValues

---

Regards,
mg

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?

My understanding is that

Code:

---

.Copy Destination:=

---

bypasses the clipboard therefore there is nothing to paste.
So you have to do it as two separate opertions

Another and faster solution is to copy the range without using the clipboard:

Code:

---

    Dim rngS As Range   
    Set rngS = Sheet1.Range("A1").CurrentRegion
   
    With rngS
        Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

---

This 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

Code:

---

    Dim rngS As Range
   
    Set rngS = Sheet1.Range("A1").CurrentRegion
   
    With rngS
        Sheet2.Range("A1").Resize(.Rows.Count, 2).Value = .Value
    End With

---

Artik

Hi Paul,Fluff and Artik:thumb,

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

Regards,
mg.

You're welcome & thanks for the feedback