<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
3,548 Points
548 Views
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. 


Done

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!

CopyShapes.xlsm

0
Comment
Author:Martin Liss
0 Comments

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month