When you first begin learning how to create macros, you often start with macros that need to work in only a particular workbook. But eventually you will begin creating macros that you want to use in other workbooks?

You can make these macros available every time you open Excel by storing them in a 'Personal Macro Workbook'. This is a hidden workbook stored on your computer, which opens in the background every time you open Excel, allowing you to run any of its macro in any open workbook.

Typically, you will then create commands/buttons on the Quick Access Toolbar or on the Ribbon which you can use to run macros from your PMW.

Do you have a Personal Macro Workbook?

To check if you already have a Personal Macro Workbook, from the View tab select Unhide. If PERSONAL.XLSB is listed, you have one. If not, you can easily create one. Click Cancel on the Unhide dialog.

Create a Personal Macro Workbook

The simplest way to create a Personal Macro Workbook is by recording a 'dummy' macro.

1) Click the Record Macro button on the bottom left side of the Status Bar (next to the Ready message).

2) In the Record Macro dialog select Personal Macro Workbook from the 'Store macro in' dropdown and click OK.

3) The record macro button on the StatusBar has changed to a Stop button. Click it to stop recording the macro.

4) Next hold down ALT key and press F8 key to open the Macro dialog.

5) Select the macro (probably Macro1) from the list and click the Step Into button. The Visual Basic Editor (VBE) window opens.

6) In the VBE window, from the Run menu, select Reset.

7) You will see the Macro code that was recoded in the Code window.


8) Select this code and press the Delete key.

9) Finally, save your Personal Macro Workbook by clicking the Save button on the VBE toolbar.

You can now record new macros in your Personal Macro Workbook or open your PMW and paste existing VBA code into it.

Note that the Personal Macro Workbook is a hidden workbook, so when you close the VBE window, the workbook remains open but is hidden. Since the workbook is still open, you are not prompted to save the changes you made to your Personal Macro Workbook when you close the VBE window. However, when you attempt to close Excel, you WILL be prompted to save any changes. I personally prefer to save changes (click the Save button on the toolbar) to my Personal Macro Workbook before closing the VBE window because, on too many occasions, I have accidentally clicked No to save changes when shutting down Excel later in the day and lost my work.

To share this tip with your friends and
colleagues, choose one of these options...

To get more tips like this one every week...

Sign up for my FREE weekly Newsletter

'Spreadsheet Tips From An Excel Addict'