Make Macros Available in All Excel Files
| » Key to Instructions | ||
| To help readers follow the instructions in this article, we used two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer. | ||
MAKE MACROS AVAILABLE IN ALL EXCEL FILES 
I created a macro, but when I tried to use it later in another file, it disappeared. Then, when I went back to the file in which it was created, it was available again. What did I do wrong?
You did everything right—that is, you did everything right if all you wanted to do was use that macro in the file in which it was created. But if you want the macro to be available whenever you launch Excel, you need to place it in the Personal Macro Workbook .
I can anticipate your next question: “So how do I create this Personal Macro Workbook ?”
As with many Windows functions, there are many ways to reach the same goal. Here is the easiest way: Open a new Excel file and click on Tools and Record New Macro , which opens the Record Macro screen. Under Macro name , label it something innocuous (such as Test ) and in the box under Store macro in , click on the down arrow to show Personal Macro Workbook and then click on OK (see screenshot below).
Before you can move on, a dialog box asks you to confirm your decision (see screenshot). Click on Yes .
Then, in the Record Macro box click on OK , which signals that it’s ready to record your keystrokes. Since this macro’s only purpose is to create the personal macro workbook, click any cell and then click on Stop dialog (see screenshot).
Now whenever you create a macro in Excel, it automatically saves it in your new Personal Macro Workbook .
Bonus: In addition to being able to trigger a calculation process, macros can be recruited to store boilerplate or any repetitive data you may wish to conveniently insert into a spreadsheet cell. To do that, simply copy the data into a new macro, following the same steps as above, and a single click will pop it into a cell of your choosing.
For more on macros and the Personal Macro Workbook , see “ Automate Excel Functions” ( JofA , Jan. 05, page 46).
