When you develop a macro in Excel, it’s only available in the workbook you create it in – unless you add it to your Personal Macro Workbook. The workbook is a hidden workbook that runs in the background whenever you open Excel, allowing you to access any of the macros you’ve stored within it.
It’s a great place to store those macros that you want to reference often, allowing you to automate repetitive tasks.
Setting up your Personal Macro Workbook
By default, the workbook doesn’t exist. The easiest way to create it is to record a blank macro and ask Excel to store it in the personal macro workbook.
Select the Record Macro button and ask Excel to save the macro in your Personal Macro Workbook.
Once it’s recording, simply select Stop Recording. Your macro will be completely empty and do absolutely nothing, but you’ll have created your work.
Next, close out of Excel entirely and select Save when prompted to save your Personal Macro Workbook.
Now when you open Excel, the Personal Macro Workbook will open in the background, allowing you to access any of the macros you save in there in any workbook you want.
Adding Macros to your Personal Macro Workbook
Once your workbook is all set up, you can begin adding macros to your workbook. To do this, simply select the Visual Basic button in the Developer tab.
From there, select Module 1 under PERSONAL.XLSB object and paste your macro into there.
Ideas for your Personal Macro Workbook
Really what you want to add to your Personal Macro Workbook is up to you, but we’ve included some straightforward examples of what you could include. These are some of the things we typically need to do when opening workbooks from colleagues or those downloaded from online.
Unhide all Columns
Sub UnhideColumns() Columns.EntireColumn.Hidden = False End Sub
Unhide all Rows
Sub UnhideRows() Rows.EntireRow.Hidden = False End Sub
Sub AutoFitColumns() Cells.Select Cells.EntireColumn.Autofit End Sub
Sub AutoFitRows() Cells.Select Cells.EntireRow.Autofit End Sub
Unmerge all Cells
Sub UnmergeCells() Cells.Select Cells.Unmerge End Sub
Unhide all Worksheets
Sub UnhideWorksheets() Dim ws as Worksheet For Each ws in ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Delete all Worksheets but the Active Worksheet
Sub DeleteSheets() Dim ws As Worksheet Application.DisplayAlerts = False For Each ws in ThisWorkbook.Worksheets If ws.name <> ThisWorkbook.ActiveSheet.name Then ws.Delete End If Next ws Application.DisplayAlerts = True End Sub
How to use Personal Macro Workbook Macros
Once you’ve had some time to add your macros to your Personal Macro Workbook, you can easily access them by select Macros under the Developer tab. All the ones you’ve added will show up there, including any macros that you may have added to the workbook you’re currently working in.
Adding your Personal Macro Workbook Macros to your Ribbon
If you find yourself referencing the macros quite often, you can also add them to the ribbon within Excel, or even make a dedicated ribbon group for just your macros.
Right click the toolbar and select Customize Ribbon. To add a new ribbon group, select New Tab and give it a name. In the example below, we’ve called it Macros.
You can add a Group to it as well, in order to help organize your macros more logically. To do this, simply click New Group and give it a name.
To add your macros to the tab, simply select the tab or group. On the left, select the dropdown where Popular Commands is selected by default, and switch to Macros. Your macros will all appear, allowing you to select the right arrow to move the order.
They’ll be given PERSONAL.XLSB! prefix when you move them over. Simply select the Rename button and you can give it any name you choose, as well as choose an icon for it.
Here’s a view of what you can end up with. Organizing your macros by categories and adding useful icons and make them easy to reference.
Want to learn some more about Personal Macro Workbooks? Check out the official documentation!