Make Your Macros Available in All Workbooks in Excel

Add to Favorites
Author: | Edits: don

In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook.

To do this we need to create what is called a "personal macro workbook" and then save it.  All of the macros that we want to use in Excel will be stored within this personal macro workbook and will then be accessible by any Excel file.

Steps to Make any Macro Available in All Excel Files

  1. Open an Excel file and then go to record a dummy macro, recording this dummy macro is what will create the "personal macro workbook" that we need in order to store the macros.
  2. To record a macro go to the Developer tab, if that is visible, or simply look to the bottom left of the Excel window.  From there, click the Record Macro button:
  3. On the screen that appears, make sure to select Personal Macro Workbook from the Store macro in: drop down box and then hit the OK button. Nothing else matters here because we just need to record a simple macro, anything really.
  4. Select any cell in the worksheet and then just click the Stop Recording macro button, which is in the exact same location as the Record Macro button in step 2.
  5. Now hit Alt + F11 on the keyboard so we can go to the VBA/Macro editor window.  You will now see the PERSONAL.XLSB file appear in the top left pane of the window.
  6. All you have to do now is to add Modules and Macros to this file like you would to any normal Excel file. Double-click Module 1 and we see the macro that we just recorded:

As you can see, this new PERSONAL.XLSB file behaves just like a regular file in the VBA window.  This is where you will store any macros that you want to be able to access from all Excel files.


The personal macro workbook file does NOT travel with your Excel files when you send them; this PERSONAL.XLSB file only remains on your computer for you to use.  So, if you send a workbook to another person, they will not be able to access the same macros that you have unless you also put those macros in the workbook you sent.

You must follow the above steps to create the personal macro workbook but, once you have created it, you can access it from any workbook on your computer by simply going to the VBA/Macro editor window (Alt + F11).  As such, once it is created, adding macros to this new file is the same as adding it to any other Excel file - open any workbook, go to the VBA window, add a module to the PERSONAL.XLSB file and then add a macro.

The sample file for this tutorial is empty because, remember, you cannot send the personal macro workbook file with Excel files.

Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Vlookup Function That Searches The Entire Workbook in Excel - UDF
Macro: With this VLOOKUPWORKBOOK function, you will have to power to more quickly and easily ana...
Copy and Paste Data using Macro VBA in Excel
Tutorial: How to copy and paste data using a Macro in Excel. I'll show you multiple ways to do this,...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Highlight, Sort, and Group the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allows...
Sort Data that Doesn't Have Headers in Ascending Order in Excel
Macro: Sort data that doesn't have headers in ascending order in Excel with this macro. This is a...
Loop through a Range of Cells in a UDF in Excel
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...