VBA Excel copy multiple shapes to clipboard

Question

I have an Excel sheet with four shapes and a couple of buttons to run macros. The shapes are named topCircle, leftCircle, rightCircle & midCircle.

Wanting one of the buttons to run a macro that copies the four shapes to clipboard, for pasting into other Office docs.

Have been though various MSDN articles (like https://msdn.microsoft.com/en-us/library/office/ff940375.aspx) but having trouble. Trying to imitate the example on the linked article, I wrote this:

Set myDocument = Worksheets("Overall")

myDocument.Shapes.Range(Array("leftCircle", "rightCircle", "topCircle", "midCircle")).Copy

But getting an error "Object doesn't support this property or method". Have also tried a few other things without luck. Any ideas as to how to get this to work? Cheers!

Even though I don't like to use Select, if you change to myDocument.Shapes.range(Array("leftCircle", "rightCircle", "topCircle", "midCircle")).Select and after add Selection.Copy it will work. Let me "play" a little more to avoid this "ugly" work-around — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2017-02-21T06:15:46

It seems that to copy multiple shapes from an Excel worksheet you have 3 options:

Option 1: Use Select first to select all Shapes, and then copy the Selection :

myDocument.Shapes.Range(Array("leftCircle", "rightCircle", "topCircle", "midCircle")).Select
Selection.Copy

Option 2: Use the Group to group all selected shapes, then copy them together, and afterwards use the Ungroup to split them back:

With myDocument.Shapes.Range(Array("leftCircle", "rightCircle", "topCircle", "midCircle"))
    .Group.Copy
    ' paste to wherever you want

    .Ungroup
End With

Option 3: use For Each MyShape In myDocument.Shapes to loop through all Shapes in myDocument worksheet, and if it mathces one of the Shape.Name you wanted it, copy it:

Dim MyShape     As Shape

For Each MyShape In myDocument.Shapes
    Select Case MyShape.Name
        Case "leftCircle", "rightCircle", "topCircle", "midCircle"
            MyShape.Copy
            ' paste to wherever you want

    End Select
Next MyShape

VBA Excel copy multiple shapes to clipboard

1 Answers