CODEWITHSUNDEEP
vbaexcelrange
WVrock
WVrock

Reputation: 1765

Copy Method not Working when Range is set to a Variable in Excel VBA

I want to copy a range into another range in different sheets. I wanted to assign variable names to the ranges so I can do multiple things with the same ranges without rewriting them again.

This code is working

 Worksheets(yeniListSheet).Range("A" & y & ":" & "H" & y).Copy (Worksheets(eskiListSheet).Range("K" & eskiLisRowNum))

But this code is not working 

Dim fromR, toR As Range
Set toR = Worksheets(eskiListSheet).Range("K" & eskiLisRowNum)
Set fromR = Worksheets(yeniListSheet).Range("A" & y & ":" & "H" & y)
fromR.Copy (toR)

The error is "copy method of range class failed"

What is the problem in the 2nd code? How am I supposed to write it like the 2nd code?

Upvotes: 1

Views: 455

Answers (1)

Vityata
Vityata

Reputation: 43585

  • Declare like this: Dim fromR As Range, toR As Range. Otherwise only the last toR gets declared as a Range, the fromR is a Variant.

  • Remove the parenthesis from (toR). It causes an error, because the parenthesis force taking the parameter ByVal. And a Range object is passed by reference.

Then try this, hardcoding the variables, to make sure that you have correct values for eskiLisRowNum and y:

Public Sub TesMe()
    Dim fromR As Range, toR As Range
    Set toR = Worksheets(1).Range("K" & 2)
    Set fromR = Worksheets(1).Range("A" & 1 & ":" & "H" & 2)
    fromR.Copy toR
End Sub

Upvotes: 1

Related Questions