Quickly and Easily Copy Shapes and Their Assigned Macros to a New Location

Published on
3,109 Points
Last Modified:
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.

After you download and open the attached "CopyShapes.xlsm" workbook you'll see a 'Copy Shapes' button in the middle of the screen, and when you click it you'll see something like this.

What's What

Workbook (Source)

The 'Workbook' list under 'Source' is automatically populated with all the open workbooks, and in this case there are two. CopyShapes.xlsm (or whatever you call it if you rename it) is not shown. Since the purpose of this tool is to simplify the moving of shapes there needs to be at least one workbook open and just one would be fine if you are planning to copy a shape or shapes from one sheet to another or even duplicate the shape on the same page. 

Worksheet (Source)

When one of the source workbooks is clicked, all the worksheets in that workbook show up.

Shape Captions

I once thought that a shape was just a shape and that shapes were only the things that you can create when you click 'Shapes' from the 'Insert' tab, but I was wrong and actually there are a bunch of other objects that are considered shapes and they include comments, charts, and even data validation dropdowns among others. This tool lists only those inserted from  Insert->Shapes. If a shape doesn't have a caption then "<None>" will be displayed. 

Please see my "Deleting Shapes - A Warning" article for a caution about deleting shapes.

Macro Name

This is a list of the macros assigned to a particular shape. If that shape doesn't have a macro assigned to it the entry will say "<None>".

Copy Code?

When a shape is selected and it has an assigned macro, a "Yes" will appear for that shape which means that the macro will be copied to the destination workbook. If you don't want to copy the macro you can click on the "Yes" and it will change to "No". Copied macros will be placed in a code module named 'ShapeMacros' which will be created if it doesn't already exist.

Workbook (Destination)

This is automatically populated with the same workbooks as above.

Worksheet (Destination)

When one of the destination workbooks is clicked, all the worksheets in that workbook show up.

Copy Shapes to Destination

The button will become enabled when a source workbook and worksheet, at least one macro, and a destination workbook and worksheet have been selected, and when it's clicked the selected macros and code will be copied to the destination workbook. 

If during the process it is found that a shape with the same name already exists you will be given the chance to either change the name, leave it as is, or cancel the copying of that shape, Similarly if it is found that a macro with the same name already exists you will be given the chance to either change the name, leave it as is, or cancel the copying of the macro.

A copied shape will normally have the same position on the destination sheet as it has on the source sheet. I say normally because if you copy the same shape twice, the second one will be placed 25% of its width to the right of the first so that it is easier to find.

Ready to Go 

In this case, pictured below, I'm planning on copying shapes from the 'Button Source.xlsm' workbook to the 'DestWorkbook.xlsm' workbook and the picture shows the result of clicking 'Button Source.xlsm', clicking it's one worksheet, selecting the shapes I want to copy and selecting the destination workbook and one of its sheets. 


The only other thing you need to do is to click the 'Copy Shapes to Destination' button!

Where's My Shape?

As mentioned above, the copied shapes are placed in the same position they were copied from, but if you can't find them then click Home->Find & Select->Selection Pane. In case you aren't familiar with the Selection Pane, here are some tips. 

  • Selecting one selects it on the page so you can locate it
  • It shows all the shapes on the page in a neat list
  • You can easily delete one by selecting one from the list and pressing the Delete key
  • Or rename it by double-clicking it and typing in the new name
  • Or hide it by clicking the "eye" icon at the right of the name (clicking it again makes it visible)

If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. 

If you have any suggestions for improvement or if you encounter any bugs, please send me a message. Thanks!


Author:Martin Liss

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Join & Write a Comment

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month