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()
I get the following error when using the first two options, and would
like to know why this is so.
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 SubDim 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
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.Unable to get the PasteSpecial property of the Range class