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.



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



Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.