Setting up a Personal Macro Workbook in Excel (and some sample macros!)

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

Unhide all Rows

Sub UnhideRows()
Rows.EntireRow.Hidden = False
End Sub

Autofit Columns

Sub AutoFitColumns()
Cells.Select
Cells.EntireColumn.Autofit
End Sub

Autofit Rows

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!

Want to learn more about Excel tricks? Check out our posts, such as Using VLOOKUP or our full archive for Excel tutorials.

Additional Resources

To learn more about related topics, check out the tutorials below:

• Create Dropdown List in Excel
• How to Use VLOOKUP in Excel