vba method copy of object range failed

Question

I am simply trying to copy one range on a worksheet to another range on a different worksheet. From what I understand the method is Range1.Copy(DestRange) Do I need to specify the worksheets, like this? Worksheets("sheet1").Range1.Copy(Worksheets("Destination").DestRange)

My code throws an error: Runtime Error 1004: Method 'Copy' of Object 'Range' failed

My Code:

Sub adsadsf()

Dim currentR As Range
Dim rawIput As Worksheet
Dim excitationWs As Worksheet

Set currentR = Range("B2", Range("B2").End(xlDown))
currentR.Select
Set rawInput = Worksheets("rawInput")
Set excitationWs = Worksheets("Excitation Curve")

'none of these work. all throw same error
'currentR.Copy (Range("H1"))
'currentR.Copy (Range("H1"))
'currentR.Copy (excitationWs.Range("A1"))
rawInput.Copy (excitationWs.Range("A1"))

End Sub

currentR.Copy Range("H1") without brackets. Also rawInput.Copy (excitationWs.Range("A1")) should be rawInput.Cells.Copy excitationWs.Range("A1") if you want to copy all cells (unefficient - better to copy sheet). This might be interesing: What is the difference between entering parameters in these four different ways — Dmitry Pavliv
@simoco - How do you expect to get the big points if you answer the question in a comment? (+1) — dcromley
@simoco Thank you! This has been driving me up a wall and I didn't really understand the logic between some methods having parenthesis and then suddenly having parenthesis causes everything to go to hell. you are awesom — Mike

bmgh1985 bmgh1985 · Accepted Answer · 2014-06-06T15:19:06

Try this (you can add the worksheet dims back in if needed)

Dim currentR As Range
Dim destR As Range

Set currentR = Worksheets("rawInput").Range("B2", Range("B2").End(xlDown))
Set destR = Worksheets("Excitation Curve").Range("A1")

currentR.Cells.Copy destR

vba method copy of object range failed

1 Answers