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. 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. In the Record Macro dialog box, specify a name (default is fine too). In the ‘Store Macro in’ drop down, select 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:
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). In the VB Editor, within the Project Explorer, you will see the PERSONAL.XLSB object. Double-click on Module 1. 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.
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.
Working with Cells and Ranges in Excel VBA. Working with Worksheets in Excel VBA. Working with Workbooks in Excel VBA. Creating a User Defined Function in Excel using VBA. Excel VBA Events – An Easy (and Complete) Guide. Using Loops in Excel VBA. If Then Else Statement in Excel VBA. How to Create and Use an Excel Add-in.
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.
