3

I want to copy all shapes from one worksheet and paste them on another worksheet at the same position. The shapes can be rectangular callouts or pictures.

So far, I know how to loop through all shapes in my old worksheet:

Dim s As Shape For each s in Activesheet.Shapes ... Next

How do I copy and paste the shapes at the same position in another worksheet, say Sheets("new")?

Improve this question

2 Answers 2

Reset to default
5

The below code should get you going. Be aware that I'm using the internal sheet name in the code. (Sheet1 and Sheet2. The names before the brackets in the Project Explorer)

I used a bit of a workaround to avoid working with selections: You need to set the name of the shape first, because if it still has the standard name (e.g. "Oval 3") the name gets changed ("Oval 4"). In the end you can restore the original name of the shapes in both sheets.

Sub CopyShapes()

    Dim s As Shape
    Dim OriginalName As String

    For Each s In Sheet1.Shapes
        OriginalName = s.Name
        s.Name = "FixedName"
        s.Copy
        Sheet2.Paste
        Sheet2.Shapes("FixedName").Top = s.Top
        Sheet2.Shapes("FixedName").Left = s.Left
        s.Name = OriginalName
        Sheet2.Shapes("FixedName").Name = OriginalName
    Next s

End Sub

Edit: Adjusted the code to avoid the use of Selection. as required in the comments

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sorry, wrong shot, it does not work. I get an error message that I cant modify the "top" property of a range-object. Apparently "selection" does not give me the handle for the shape object.
I adjusted the code in my answer to avoid the use of Selection.
0

As far as I'm aware, there is no specific method to copy all of them. You could try the regular .Copy Destination:=... method, but I'm not positive this will work.

The alternative is to just generate a new shape on the new sheet with identical properties to the desired shape. As you loop through the shapes on your current sheet, you would just need to create new shape objects with all of the same properties.

The most effective method (although this depends slightly on your intentions) is just to copy the original worksheet, instead of generating a fresh sheet. This will pull all the shapes (and other data) to the new sheet with all location and properties kept identical. If you just need the shapes and none of the cell data, you can copy the sheet and then add NewSheet.UsedRange.ClearContents which will remove all the data but leave formatting intact.

Improve this answer

13 Comments

You can also just use .Clear if you don't want to preserve formatting
To copy the shapes does not really suit me needs: I have a template in Excel, where the layout changes from time to time. The script shall transfer all the information from the old templates (about 150) to the new template format including annotations that come in the form of callouts.
@derMax I'm confused. You need to copy more than the shapes or only the shapes?
The template is kind of a userform, where most information is stored in the cells. All cells that are not intended to be edited by the user are locked. I need to transfer all the information from editable cells as well as all shapes on the chart. I already did the first task - now the shapes are what I havent achieved so far.
@derMax Well using the third method i suggested would do all of that for you, but if not, try the second method. Did you try it and it didn't work for you?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.