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("t emplate")
Set t_sht = ThisWorkbook.Worksheets("t arget")
s_sht.Shapes.Range(Array(" Rectangle 25", "Rectangle 26")).Select
Selection.Copy
t_sht.Activate
t_sht.Range("A1").Select
ActiveSheet.Paste
Dim s_sht As Worksheet
Dim t_sht As Worksheet
Set s_sht = ThisWorkbook.Worksheets("t
Set t_sht = ThisWorkbook.Worksheets("t
s_sht.Shapes.Range(Array("
Selection.Copy
t_sht.Activate
t_sht.Range("A1").Select
ActiveSheet.Paste
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
Norie,
Apparently the grp.Ungroup did not work. The grouped shapes still show as "Group 4" (excel named I believe).
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?
Did you change anything in the code?
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
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
ASKER
Run-time error '1004': Grouping is disabled for the selected shapes.