When you record or write a macro in Excel, you can choose to store the macro in “the current workbook” or “The Personal Macro Workbook”.
If you store the macro in the current workbook it is only available when that workbook is open. You would do this when the macro automates tasks that are specific to that file. However, what if you want a macro that you can use (or at least have available) in all your Excel files?
You could copy the macro from one file to another, however, apart from being time-consuming, if you need to make a change to the macro, you’d have to do it in every workbook.
This is where the Personal Macro Workbook comes in. It’s an Excel file called personal.xls (in Excel 2007 onwards it’s called personal.xlsb). The file must be located in a specific location:
On Windows Vista and Windows 7 it lives in C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\
On Windows XP it lives in C:\Documents and Settings\Application Data\Microsoft\Excel\XLSTART\
On OSX the file is stored in ~/Library/Application Support/Microsoft/Office/Excel
The Personal Macro Workbook isn’t part of the Excel installation. In fact it doesn’t exist until you create it. Although there are 2 ways to create a Personal Macro Workbook, the easiest way is to record a new macro and when prompted to save it, select Personal Macro Workbook:
As soon as you’ve done this, Excel starts recording. Simply stop the recorder without performing any keystrokes or mouse clicks (the empty macro can be deleted later).
When you exit from Excel, you are prompted to save the Personal Macro Workbook:
Click Save.
From now on, every time you run Excel, the Personal Macro Workbook will be loaded. When it was saved, it was saved as a hidden file, therefore you don’t actually see it when you are using Excel.