Make Macros Available in All Excel Files

BY STANLEY ZAROWIN

 »  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).

 

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.