Easy Steps to Save Your Macros to a Workbook

June 25, 2014 Excel  No comments

A macro is a series of keystrokes, mouse clicks, and other commands that you can record and reuse to save time and avoid unnecessary repetition. But once you create it, how do you save it so you can use it in other Excel workbooks? Follow these easy steps to save your macros to a workbook.

Your Personal Macro Workbook

By default, when you create a macro in Excel, it only works for the workbook that it’s created in. If you find that you are recreating the same macros over and over again, you can save time by copying those macros to a special workbook called Personal.xlsb that is saved to your computer. Any macro that you store in your personal workbook is available to use in any workbook whenever you use Excel on that computer, because the personal workbook is opened as a hidden workbook each time you start Excel. Your personal workbook is saved as Personal.xlsb in the path C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART (on Windows 7 and Windows Vista computers). To start using your personal workbook, you need to start by creating a new macro and storing it in your personal workbook.

Creating a Macro to Store in Your Personal Workbook

First, ensure that your “Developer” tab is shown on your ribbon. Next, on the “Developer” tab, click on the “Record Macro” button. When the “Record Macro Dialog Box” appears, name your macro and in the “Store macro in” box, select “Personal Macro Workbook” and click “Ok”. Record your macro, and then click on the “Stop Recording” button on the “Developer” tab. Now, close your open workbooks and exit Excel. You’ll be prompted to save the changes you made to the Personal Macro Workbook, click “Yes” to save your macro to your Personal workbook.

Using Your Stored Macro from Your Personal Workbook

To use your stored macro, go to the “View” tab and in the “Window” group, click on the “Unhide” button. The “Unhide Dialog Box” will appear and you should see “PERSONAL.XLSB”, click “Ok” to view your personal workbook. Now you will be able to use your saved macros in your current Excel workbook. If you make any changes to your macro, you will be prompted to save those changes when you close your workbook.

We here at DTA hope you feel comfortable with using Excel financial formulas. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work. Subscribe to our blog to receive weekly Desktop tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online trainings.

Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Desktop Training Academy

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>