Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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.
When one of the source workbooks is clicked, all the worksheets in that workbook show up.
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.
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>".
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.
This is automatically populated with the same workbooks as above.
When one of the destination workbooks is clicked, all the worksheets in that workbook show up.
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.
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!
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.
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!