When you first create a macro in a workbook, it works only in that workbook. But what if you want to use the macro in other workbooks? To make your macros available every time you open Excel, you can create them in a workbook called Personal.xlsb. That’s a hidden workbook stored on your computer, which opens every time you open Excel.
Before you get started, make sure the Developer tab is shown on the ribbon. If it’s not there, do the following:
Click File > Options > Customize Ribbon.
Under Customize the Ribbon, in the Main Tabs box, check the Developer box, then OK.
Next, create a macro. We’ll record a very simple example that will format the text in the current cell as bold.
You can learn more about creating macros in Quick start: Create a macro.
In cell A1, enter “Some text,” and then press Enter.
Click Record Macro.
In the Record Macro dialog box, type a name for the macro in the Macro name box. For example, type BoldMe.
Don’t use any spaces in the name.
In the Store macro in box, pick Personal Macro Workbook > OK. This is the most important step, because if you don't already have a Personal Macro Workbook, Excel will create one for you.
Click in cell A1 and press Ctrl+B to apply bold formatting.
That’s the only step in the macro.
Click Developer > Stop Recording.
Close any open workbooks, and then exit Excel.
A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.
Click Save to save the personal workbook.
The next time you open Excel, the macro you created is available. To see it:
Click Developer > Macros to launch the Macro dialog.
Our example macro is called PERSONAL!.BoldMe.
If you don’t see the macro, in the Macros in box, click PERSONAL.XLSB.
To run the macro, see Run a macro.
Moving macros from one computer to another
Say that you get another computer and want to move or copy all of your macros to that computer, or you want to share your macros with someone else. It’s not possible to share your Personal.xlsb between computers, but you can copy it to the XLSTART folder on other computers or copy some or all of its macros to the Personal.xlsb file on other computers. In Windows Explorer you can search for XLSTART to locate the folder.
If you have one or just a few macros that you want to share with others, you can send them the workbook that contains it in an email message. You can also make the workbook available on a shared network drive or from a SharePoint Services library.
For more information about copying a macro from one workbook to another, see Copy a macro module to another workbook.
Unhide your personal workbook
Any macros you save to the personal workbook can be edited only by first unhiding the personal workbook. When you start Excel, the personal workbook is loaded but you can’t see it because it’s hidden. To see it:
Click View > Unhide.
In the Unhide dialog box, you should see PERSONAL.XLSB.
Click OK to view the personal workbook.
To hide the personal workbook, make sure you have Personal.xlsb selected, and then click Hide on the View tab.
PERSONAL.XLSB will always be editable in the Visual Basic Editor (VBE).
Any time you create a new macro and save it in your personal workbook or update any macros that it contains, you are prompted to save the personal workbook just as it did the first time you saved it.