VBA: Why isn't variable working in named range?

Question

This is part of a larger code, but this snippet isn't working. I'm trying to just set two cells equal to each other, but it's not working. When I use the .Range("v1_copy"), the code runs, but when I name that range and place it as a variable (myCopyRange), the code doesn't run and I get the error: Compile error: Method or data member not found. Any help would be appreciated!

Sub copy_paste_test()

Dim myCopyRange As Range
Dim myPasteRange As Range
Dim myWS1 As Worksheet
Dim myWS2 As Worksheet

Set myWS1 = Sheets("Sheet1")
Set myWS2 = Sheets("Sheet2")

myCopyRange = Range("v1_copy")
myPasteRange = Range("v1_paste")

'myWS2.Range("v1_paste").Value = myWS1.Range("v1_copy").Value
' This line works, but the below line doesn't
 myWS2.myPasteRange.Value = myWS1.myCopyRange.Value
' This should be the exact same, just substituting the variable, but doesn't work

End Sub

Answer 1

You're missing the Set keyword for your Range object reference assignments to myCopyRange and myPasteRange.

But for retrieving a named range, the best place to go if you want fully explicit code that does what it says and says what it does, is to dereference the Name from the appropriate Names collection.

If the names are workbook-scoped, qualify with a Workbook object - here a book object variable, but depending on needs ActiveWorkbook or ThisWorkbook work just as well:

Set myRange = book.Names("name").RefersToRange

If the names are worksheet-scoped, qualify with a Worksheet object - here a sheet object variable, but ActiveSheet works just as well:

Set myRange = sheet.Names("name").RefersToRange

That way the code won't break if the workbook is renamed, or if the user changes the "tab name" of the sheet. It won't break as long as the name exists in the queried Names collection.

---

'myWS2.Range("v1_paste").Value = myWS1.Range("v1_copy").Value
' This line works, but the below line doesn't
 myWS2.myPasteRange.Value = myWS1.myCopyRange.Value
' This should be the exact same, just substituting the variable, but doesn't work

This should be the exact same - no. myWS1.myCopyRange is illegal: myWS1 is a Worksheet object: the Worksheet interface doesn't have a myCopyRange member, hence method or data member not found.

Since myCopyRange is a Range object, it knows about its Parent which is the Worksheet it belongs to: there's no need to qualify it... and there's no need to dereference it again either - this is enough:

myPasteRange.Value = myCopyRange.Value