Store Excel Macros in Your Personal Macro Workbook
Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your work. You can store these general-purpose macros in the personal macro workbook so that they’re always available to you. The personal macro workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn’t exist until you record a macro using the personal macro workbook as the destination.
To record the macro in your personal macro workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list (refer to the following figure).
If you store macros in the personal macro workbook, you don’t have to remember to open the personal macro workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the personal macro workbook.
The personal macro workbook normally is in a hidden window to keep it out of the way.