VBA Excel - copy object to last active sheet-CodePudding

I have an object located on a different sheet, which I would like to copy to the last active sheet. Unfortunately, the code I have throws an error:

Object doesn't support this property or method

Sub AddCabinet()
Dim MooSheet, CurrentSheet As Worksheet
Set CurrentSheet = ThisWorkbook.ActiveSheet
Set MooSheet = ThisWorkbook.Sheets("Cab Templates")

MooSheet.Shapes.Range(Array("VHPOPA")).Select
Selection.Copy
CurrentSheet.Range("A1").Paste
End Sub

How can I copy an object to my previous current sheet? I have a few sheets with the same buttons.

CodePudding user response：

.paste is a worksheet method, not a range method, that is where the error is coming from.

We can remove all the Selects to make this a bit cleaner.

    Dim MooSheet As Worksheet, CurrentSheet As Worksheet
    Set CurrentSheet = ThisWorkbook.ActiveSheet
    Set MooSheet = ThisWorkbook.Sheets("Cab Templates")
    MooSheet.Shapes("VHPOPA").Copy
    CurrentSheet.Paste

CodePudding user response：

Before copying the object need to be there in that sheet, solve the problem... try this...

Sub AddCabinet()
Dim MooSheet, CurrentSheet As Worksheet
Set CurrentSheet = ThisWorkbook.ActiveSheet
Set MooSheet = ThisWorkbook.Sheets("Cab Templates")

MooSheet.Select
MooSheet.Shapes.Range(Array("VHPOPA")).Select
Selection.Copy

CurrentSheet.Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Hope it Helps...