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.

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.
    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.
    Record Macro dialog box

  4. Click OK.

  5. Perform the actions that you want to record.

  6. On the Developer tab, in the Code group, click Stop Recording.
    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.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Top of Page

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×