Link to home
Start Free TrialLog in
Avatar of COPUSER
COPUSER

asked on

Copy and paste Excel Shapes using vba

I want to copy Excel Shapes in one sheet and paste it to another sheet in the same workbook. The following code do not work.  

    Dim s_sht As Worksheet    
    Dim t_sht As Worksheet
   
    Set s_sht = ThisWorkbook.Worksheets("template")    
    Set t_sht = ThisWorkbook.Worksheets("target")
       
    s_sht.Shapes.Range(Array("Rectangle 25", "Rectangle 26")).Select
    Selection.Copy    
    t_sht.Activate
    t_sht.Range("A1").Select
    ActiveSheet.Paste
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of COPUSER
COPUSER

ASKER

Thanks, Norie.  The code works the first time.  When I rerun the code with a different target cell address I get the following error message

Run-time error '1004': Grouping is disabled for the selected shapes.
Where do you get the error?

That error is sometimes caused when you try to group shapes that are already grouped, the code I posted does group shapes but it also ungroups them so you should end up with no grouped shapes.

Do you have any other shapes that are grouped?
Avatar of COPUSER

ASKER

Norie,
Apparently the grp.Ungroup did not work. The grouped shapes still show as "Group 4" (excel named I believe).
I don't see why that wouldn't work, did you get an error on that line?

Did you change anything in the code?
Avatar of COPUSER

ASKER

Norie,

 I ran the following sub before and after the copy code and everything works the way it should.

Sub UngroupAll(sht As Worksheet)
    Dim s As Object
    For Each s In sht.Shapes
        If s.Type = msoGroup Then s.Ungroup
    Next
End Sub