Copy your macros to a Personal Macro Workbook

If you find yourself recreating the same macros, you can copy those macros to a special workbook called Personal.xlsb that is saved on your computer.

By default, when you create a macro in Excel, the macro works only in the workbook that contains it. This behavior is okay as long as you don’t need to use that macro in other workbooks. Any macros that you store in your personal workbook on a computer become available to you in any workbook whenever you start Excel on that same computer.

In this article


How does this work?

The personal workbook (Personal.xlsb) is opened as a hidden workbook every time you start Excel. Excel saves the personal workbook as Personal.xlsb, in the following folder on Windows 7 and Windows Vista computers:

C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART

Top of Page Top of Page

Create and update the personal workbook

To create the personal workbook, you first need to create a macro and store it in the Personal Macro Workbook. Before you get started, make sure that the Developer tab is available in the ribbon in Excel.

ShowMake the Developer tab visible in Excel 2010

  1. On the File tab, click Options, and then click Customize Ribbon
  2. Under Customize Ribbon, in the Main Tabs box, make sure that the Developer check box is selected.
  3. Click OK.

ShowMake the Developer tab visible in Excel 2007

  1. Click the Microsoft Office Button Office button image, and then click Excel Options.
  2. Click Popular, and then select the Show Developer tab in the Ribbon check box.
  3. Click OK.

Now you’re ready to record a macro and save it in the Personal Macro Workbook. In this example, you will create a simple macro by using the Macro Recorder. This macro will format the text in the current cell as bold.

  1. In cell A1, enter some text, such as “Some text.”
  2. On the Developer tab, in the Code group, click Record Macro.
    Record Macro
  3. In the Record Macro dialog box, type a name for the macro in the Macro name box, such as BoldMe. Make sure you don’t use any spaces in the name.
  4. In the Store macro in box, select Personal Macro Workbook.
    Record Macro dialog box
  5. Click OK.
  6. Press CTRL+B to apply bold formatting.
    That is only step that is recorded in the macro.
  7. On the Developer tab, in the Code group, click Stop Recording.
    Stop Recording
  8. 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.
  9. Click Yes to save the personal workbook.

The next time you start Excel, the personal workbook is loaded but you can’t see it because it’s hidden by default. You can view Personal.xlsb by doing the following:

  1. On the View tab, in the Window group, click Unhide.
    Hide command in the Window group
    In the Unhide dialog box, you should see PERSONAL.XLSB.
  2. Click OK to view the personal workbook.
    Any macros you save to the personal workbook can be edited only by first unhiding the personal workbook. This prevents you from accidentally deleting or making unwanted changes to those macros.
  3. To hide the personal workbook, make sure you have Personal.xlsb selected, and then click Hide.

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.

Top of Page Top of Page

Best practices for saving macros

It’s not possible to share your Personal.xlsb between computers, but you can copy it to the XLSTART folder 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.

If you want to copy macros from the personal workbook to another workbook or vice versa, you can do so in the Project Explorer, which is in the Visual Basic Editor. You can start the Visual Basic Editor in Excel by pressing ALT+F11. For more information about copying a macro from one workbook to another, see Copy a macro module to another workbook.

Top of Page Top of Page

 
 
Applies to:
Excel 2010, Excel 2007