Range.Paste - Object doesn't support this property or method

Question

I've got a very simple procedure that copies a range from one workbook and pastes it into another; the issue is, I'm getting the error in the title on the paste line.

Below is the code:

Sub copypasta()
Dim x As Workbook
Dim y As Workbook

Set x = ActiveWorkbook
Set y = Workbooks.Open("F:\Target\FTB\FTB.xlsx")

x.Sheets(1).Range("A1").CurrentRegion.Copy

y.Sheets("DTR").Cells.Delete '<~~~No Error Here...?
y.Sheets("DTR").[a1].Paste   '<~~~Error Here

I thought it was strange that the deletion works fine, but paste does not.

It seems that I may be missing some fundamentals of the paste method - could someone please explain what I'm missing here?

Answer 1

Other answers have provided ways to make it work, but don't explain what's going on.

y.Sheets("DTR")

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 Worksheets collections' 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:

Sub Test()
    Debug.Print ThisWorkbook.Worksheets(1).Whatever
End Sub

Same as:

Sub Test()
    Debug.Print ThisWorkbook.Worksheets.Item(1).Whatever
End Sub

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 Worksheet object:

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:

target.[A1].Paste

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 Range object:

And you get autocompletion and tooltips for everything as you type it:

Answer 2

You can Copy and Paste Ranges between sheets (and workbooks) using 1 line of code, just replace your code with the line below:

x.Sheets(1).Range("A1").CurrentRegion.Copy y.Sheets("DTR").[a1] 

If you want to use the PasteSpecial method, you got to do it in 2 lines, but you need to add a parameter after the PasteSpecial , like xlValues, etc.

x.Sheets(1).Range("A1").CurrentRegion.Copy
y.Sheets("DTR").[a1].PasteSpecial xlValues