Copy your macros to a Personal Macro Workbook

Applies To: Excel 2016 Excel 2013 Excel 2010 Excel 2007

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.

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. See Show the Developer tab for more information.

1. On the Developer tab, in the Code group, click Record Macro.
   
2. In the Record Macro dialog box, type a meaningful name for the macro in the Macro name box. Make sure you don’t use any spaces in the name.
3. In the Store macro in box, select Personal Macro Workbook.
   
4. Click OK.
5. Perform the actions that you want to record.
6. On the Developer tab, in the Code group, click Stop Recording.
   
7. Close the workbook.
   A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.
8. Click Save to save the workbook.

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.

If you want to copy macros from the personal workbook to another workbook or vice versa, you can do so 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.

About 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.