Other answers have provided ways to make it work, but don't explain what's going on.
That should really be
y.Worksheets("DTR"), because the
Sheets collection can contain non-worksheet items, such as charts for example.
Regardless, both the
Sheets and the
Item property (which is the default property of any collection type) yields an
Object, which makes every chained member call that follows, a late-bound call.
And you don't get IntelliSense on late-bound calls, since these calls get resolved at run-time, by definition: you can call anything on an
Object, and the code will happily compile:
At run-time, if VBA can't find the
Whatever member on the retrieved object's interface, it raises run-time error 438, "Object doesn't support this property or method".
Late-binding is powerful and very useful, but it also incurs overhead that you don't necessarily need.
Instead of working off an
Object, you can cast the returned object reference to a specific type when you know what that type is - in this case we know we're dealing with a
Dim target As Worksheet
Set target = y.Worksheets("DTR")
Now that you have an early-bound
Worksheet object reference, IntelliSense can guide you:
And if you try to call a bogus member (e.g.
target.Whatever), you'll get a compile-time error instead of a run-time error.
When you do this:
You're using late-binding again, to retrieve the
A1 range. Instead, call the
Worksheet.Range property getter to retrieve an early-bound
Range object - and from there you'll see that when you type
.paste there is no such thing as a
Paste method in a
And you get autocompletion and tooltips for everything as you type it: