VBA Range.Copy Range.PasteSpecial

[BigC]

I'm having trouble with the Pastespecial method in VBA, when trying to paste the values from one range into another.

Here's my code, with two lines that won't work, and the one that does.

Sub Test()

Dim r1 As Range
Dim r2 As Range
Dim r3 As Range

Set r1 = Range("_Rng1")
Set r2 = Range("_Rng2")
Set r3 = Range("_Rng3")

'#1 (Doesn't work!)
r1.Copy r2.Offset(1, 1).PasteSpecial(Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

'#2 (Doesn't work!)
r1.Copy r3.PasteSpecial(Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

'#3 (This works, but seems unnecessarily cumbersome & long)
r1.Copy
Set r2 = r2.Offset(1, 1)
r2.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False
​

End Sub

I get the following error when using the first two options, and would
like to know why this is so.

Run-time error '1004':
Unable to get the PasteSpecial property of the Range class

​

Thanks in advance.

 

[Michael M]

Try

Sub Test()
Dim r1 As Range, r2 As Range, r3 As Range

Set r1 = Range("_Rng1")
Set r2 = Range("_Rng2")
Set r3 = Range("_Rng3")

r1.Copy
r2.Offset(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

On another point, I'd consider not using syntax similar to cell references as set ranges.
"R1" as a range reference in a larger code, could easily be confused as a reference to cell "R1"

 

[BigC]

Hi Michael

Thanks - that works. Any idea why my option1 doesn't work, given that your solution merely splits this code into two separate lines?

I agree and understand re my syntax for Defined Names (which I discovered when initially trying to use "Rng1") - but these were simple names created for testing purposes only.

Cheers

 

[Michael M]

It's the way PasteSpecial is done as opposed to a straight Copy / Paste.
Normally you would use

r1.Copy destination:=Range(whatever)

but PasteSpecial requires specific syntax

 

[BigC]

Re: VBA Range.Copy Range.PasteSpecial [SOLVED]

Hmmm. I did notice that the VBA Help on the PasteSpecial method says that it "Pastes a Range from the Clipboard into the specified range", but didn't realize the "direct" option would not also be catered for.

Many thanks for the help.