Newbie: Error setting a range object within a worksheet

Question

I am very new to the VBA space, and am wondering why doesn't this following line of code work?

I am getting error 1004: Range of object worksheet failed

Sub MMMatch()
Dim oCell as Range
Dim r_out as Range
Dim r_in as Range
Dim ws1 as worksheet
Dim ws2 as worksheet

Set ws1 = Worksheets("MM Limits")
Set ws2= Worksheets("PivotTable")

Set r_out= ws1.range(Range("A2"),Range("A2").endxldown)
Set r_in=ws2.range(range("D2"),range("D2").endxldown)

end sub

I am getting the error on the 2nd last line. While I could add in the lines of code to select the worksheet first before setting the range object, I am wondering why wouldn't this line of code work?

Unqualified `Range` calls implicitly refer to `ActiveSheet`. Qualify them with the sheet you mean to work with (`ws1`, `ws2`) — Mathieu Guindon, May 01 '18 at 14:50

score 0 · Answer 1 · answered May 01 '18 at 14:51

You must qualify all Range objects to their parent sheets and Endxldown should be End(xlDown)

Sub MMMatch()
Dim oCell As Range
Dim r_out As Range
Dim r_in As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("MM Limits")
Set ws2 = Worksheets("PivotTable")
'the Ranges inside the outer range must match the parent sheet
Set r_out = ws1.Range(ws1.Range("A2"), ws1.Range("A2").End(xlDown))
Set r_in = ws2.Range(ws2.Range("D2"), ws2.Range("D2").End(xlDown))

End Sub

Newbie: Error setting a range object within a worksheet

1 Answers1