Important This feature isn’t available in Office on a Windows RT PC. Want to see what version of Office you're using?
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, 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.
- Click OK.
Next, create a macro. We’ll record a very simple example that will format the text in the current cell as bold. Learn more about creating macros in Quick start: Create a macro.
- In cell A1, enter “Some text,” and then press Enter.
- Click Developer > Use Relative References. That way your macro will work no matter where you type it.
- 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.
- Click OK.
- 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.
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 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.
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.
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.