Excel Personal Macro Workbook | Save & Use Macros in All Workbooks

When you create/record a macro in a workbook in Excel, it can only be used in that workbook.

But what if you have a list of useful Excel macros that you want to use in all the workbooks?  In such a case, it’s a good idea to save these in your Personal Macro Workbook.

Doing this would allow you to access the macro code from any workbook on your system.

This will save time as you don’t have to recreate the same macros again and again for every workbook. Instead, you can just create it once, store it in the personal macro workbook, and access it from any workbook.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.

What is a Personal Macro Workbook?

A Personal Macro Workbook is a hidden workbook in your system that opens whenever you open the Excel application.

Related: How to Automatically Open Specific Excel Files on Startup

It’s a place where you can store macro codes and then access these macros from any workbook. It’s a great place to store those macros that you want to use often.

To give you an example, suppose you regularly get data from your colleagues, and you’re required to clean the data and format it. Since you need to follow the same steps every time, you can create a macro to do this and save it in the Personal Macro Workbook.

Now, whenever you get the Excel file, you just need to run the macro (which is stored in the personal workbook and can be accessed from any workbook), and you’re done.

Where Can I Find the Personal Macro Workbook?

By default, the personal macro workbook doesn’t exist. You need to first create it.

Here are the steps to create a Personal Macro Workbook in Excel:

  • Open a new workbook or any existing workbook.
  • Go to the Developer tab in the ribbon.
  • Click on Record Macro.Record a Macro to create the Personal Macro Workbook
  • In the Record Macro dialog box, specify a name (default is fine too).
  • In the ‘Store Macro in’ drop down, select Personal Macro Workbook.Save Macro in Personal Macro Workbook
  • Click OK.

Note: If you can’t see the developer tab in the ribbon, here are the steps to enable it.

Doing this would create a new workbook with the name PERSONAL.XLSB and store the macro in this workbook.

Since we did absolutely nothing, the macro has no code in it. This was done to create the Personal Macro Workbook.

Now that the Personal Macro Workbook is created, you need to Close all the open workbooks. Doing this would show a prompt as shown below:

Click on Save to create the Personal Macro Workbook

Select Save.

When this is done, Excel will create and store the PERSONAL.XLSB file in the start folder, where it would automatically open in the backend whenever you open Excel.

How to Copy Macros in the Personal Macro Workbook?

Once the PERSONAL.XLSB file is created and saved, you can copy macros that you wish to reuse again.

Here are the steps to copy macros in the Personal Macro Workbook:

  • Open Excel.
  • Go to the Developer tab.
  • Click on Visual Basic option. This will open the VB Editor (or use ALT + F11).Click on Visual Basic Editor to Copy Macro in Personal Macro Workbook
  • In the VB Editor, within the Project Explorer, you will see the PERSONAL.XLSB object.VB Editor has the Personal Macro Workbook now
  • Double-click on Module 1.Double click on Module in Personal Macro Object
  • Copy and paste the macro code in the Module code window.
  • Close the Vb Editor.
  • Close and Save Excel.

The above steps would save the macros you want to reuse in the Personal Macro Workbook.

Also read: How to Remove Macros From an Excel Workbook

How to Use Macros Stored in Personal Macro Workbook?

Suppose you have a list of macros saved in the Personal Macro workbook and you want to use it on a new Excel file you get.

Here are the steps to do this:

  • Go to the Developer tab.
  • Click on Macros.
  • In the Macro dialog box, select the macro that you want to run.
  • Click on Run.

Note that the macro dialog box shows you the list of all the macros that are available for use in the open workbook. This would include the macros stored in the workbook as well as the ones stored in the Personal Macro Workbook.

You can also run a macro by assigning a keyboard shortcut to the macro or insert a shape/button and assign the macro to it.

You can also use the Personal Macro Workbook to store custom functions (user-defined functions) created in VBA.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

9 thoughts on “Excel Personal Macro Workbook | Save & Use Macros in All Workbooks”

  1. Thanks a lot! I knew about most of the excel features, but this is about *combining* them in the right way! 🙂

    Reply
  2. Thanks for your so much favour,Puneet can you tell me code to split column category wise like we have written Category as column header and under it we have data that like department accounts,store,complaints etc. How can we split our data based upon this criteria

    Reply
  3. I created a Function in my Personal.xlsb. It works, but I cannot access it just by typing =FUNCTIONNAME. I have to click on Insert Functions, More Functions, User Defined. Is there a way to make the Functions accessible as easy as =SUM…?

    Reply
  4. You can also add a tab to the ribbon to access your macros from.
    File>Options>Customize Ribbon. From the dropdown, “Choose Commands From” select Macros.

    On the other side “Customize The Ribbon”, make sure that “Main Tabs” is selected. At the bottom, click “New Tab”. You can then drag and drop your macros, name the tab and group, and assign icons to each macro.

    Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster