Summary: Learn how to create your own Personal Macro Workbook. It acts like your personal code vault where you keep frequently used macros. Any macros in the Personal Macro Workbook can be called from any workbook that’s open on your computer. You can also use it to build a library of reusable VBA code to copy and paste into other macros. This can save you lots of time.

Difficulty: Beginner

The Personal Macro Workbook (Excel 2007 – 2016)

The Personal Macro Workbook (PERSONAL.XLSB) needs to be created before you can use it for the first time.

It is a hidden workbook that is automatically opened when you start Excel.

Any code you keep in it is available in all workbooks that are open.

I highly recommend you start using it.

#1 – Personal Macro Workbook – what is it and why does it matter?

» Back to contents

Think of it as a personal code vault

Think of your Personal Macro Workbook as a personal code vault that stays in your computer.

It’s a hidden workbook that opens automatically when you start Excel.

You can run macros in the Personal Macro Workbook any time you have Excel open without needing to manually load the workbook yourself (because it’s opened by Excel on start up).

Any VBA code that you keep in this workbook will be accessible from any workbook that’s open in Excel. So you don’t need to put code in other workbooks to run the macros in your Personal Macro Workbook. You just run them from the hidden Personal Macro Workbook.

Another reason to use the Personal Macro workbook is that it’s a great place to store code samples (also known as code snippets) that you can copy and paste into your other macros. You can see it as your Personal Macro code vault (or library).

What to keep in the Personal Macro Workbook?

Anything you use that speeds up your work is a great thing to store here.

Later in section #5 I’ll show you some VBA gems that you can copy and paste into your own code vault.

In the mean time, to give you ideas… here are some macros that other Excel users have in their Personal Macro Workbooks:

• Macros to speed up data work (e.g. delete any rows with blank cells) – see NHS Excel’s list
• Macros to speed up formula work (e.g. wrap any existing formulas with IFERROR() – useful if you type that a lot) – again from NHS Excel’s list
• Macros to speed up filtering (e.g. filter on values NOT equal to active cell) – see Chandoo guest post by secret agent KV
• Macros to speed up review (e.g. create a summary sheet with all comments and highlights) – see Accounting Macros

Remember if it’s in your Personal Macro Workbook you can use it on any open workbook without putting any VBA code into that workbook.

One more thing…

Before you say “thank you very much, I going off to start using my own Personal Macro Workbook”…

… you first need to know how to create your own!

Yes that’s right, Excel doesn’t come with the Personal Macro Workbook ready to use – you need to make your own before using it as a code vault.

That’s what I’ll show you next.

#2 – Get your own – how to create a Personal Macro Workbook

» Back to contents

Here are 5 steps to get your Personal Macro Workbook.

Watch the animated GIF for the full walkthrough (1 min) and read the step-by-step instructions below.

Step 1 – Record a dummy macro in “Personal Macro Workbook”

Click the Record button on the bottom left corner of the window (next to “Ready” in the status bar) to start the macro recorder.

In the dialog box make sure you choose to store macro in “Personal Macro Workbook” then click OK.

Click the Stop button on the bottom left corner of the window to stop the macro recorder. We don’t record any actions because we just want Excel to create the Personal Macro Workbook file. (That’s why we call this a dummy macro!)

Step 2 – Check the Personal Macro Workbook exists in the VBA Editor

Before we check the Personal Macro Workbook, make sure you have the Developer tab visible in the Excel Ribbon.

While you don’t need the Developer tab to access the VBA Editor, it does give you access to useful features like Form Control buttons.

To display the Developer Tab, right click on an empty space in the Ribbon and choose “Customize the Ribbon”.

On the right hand side of the dialog box, under Main Tabs, check the “Developer” box and click OK.

In the new Developer Tab (by default at the right end of the Excel ribbon), click Visual Basic to enter the VBA Editor (or use shortcut ALT + F11).

Look on the left for the Project Explorer window.

If you can’t see the Project Explorer use the shortcut CTRL + R to display it.

In the Project Explorer you will see a VBAProject called “PERSONAL.XLSB”.

This is your Personal Macro Workbook.

You will also see “Microsoft Excel Objects” and “Modules”.

Step 3 – Delete the dummy macro

In step 1 we recorded an empty macro in the Personal Macro Workbook.

Now we can remove the empty macro as we don’t need it.

Click on the “+” next to Modules to expand that node.

Then double-click on Module1 to bring up the code window. It normally shows on the right side of the VBA Editor window.

Delete the text for the dummy macro as we don’t need it.

Step 4 – Save the Personal Macro Workbook

In the VBA Editor File Menu, click on Save (or press shortcut CTRL + S).

Any time you modify VBA code in the Personal Macro Workbook you should save it. If you forget to save it, Excel will prompt you to save on exit.

Step 5 – (Optional) Change the VBA Project name

You can change the project name to something more descriptive.

In the Project Explorer window, click on the VBA Project “PERSONAL.XLSB”.

Then press F4 twice (to edit the name in the Properties Window).

Here I have changed the name to “My_Code_Vault”. Note you can’t have spaces in the name.

#3 – Location – where is it stored?

» Back to contents

If the Personal Macro Workbook exists, you can find it in the Excel startup folder:

C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART

(where you replace Username with your own username)

You can also use the VBA Editor to display the full path on your computer like this:

• Open the VBA Editor (press ALT + F11)
• Go to the Immediate Window (press CTRL + G)
• Type this in the Immediate Window: ?Application.StartupPath
• Presss Enter

It’s useful to know this location so you can make backups of the Personal Macro Workbook (for details see section #6 – Backups).

AppData is a hidden folder

When I first looked for my Personal Macro Workbook, I couldn’t find the AppData folder.

If this happens don’t panic!

AppData is a hidden folder. To show it, follow these steps:

• Open a Windows File Explorer window (shortcut WIN + E)
• In the File menu click on Options (change Folder and Search Options)
• In the Folder Options dialog box click on the View tab
• Under Advanced Settings, select Show hidden files, folders and drives

You should now be able to view the AppData folder.

#4 – Storing and Running Macros – from the Personal Macro Workbook

» Back to contents

Add a Macro to Module1

Your new Personal Macro Workbook has one module (Module1).

You can add any VBA code here by typing it or copying and pasting code from elsewhere. Remember to open the VBA Editor first (ALT + F11).

Here are step by step instructions:

• Select the PERSONAL.XLSB project in the Project Explorer (CTRL + R)
• Click on the ‘+’ to the left of the project to expand it (you will see Objects and Modules)
• Double click on Module1 to open the code window (normally opens on the right)
• Write or Copy/Paste the macro code you want to keep in Module1
• Save the workbook using CTRL + S (or File > Save)

Run your macros in Excel

There are several different ways to run your macros.

If you’re in the VBA Editor you can:

1. Place the insertion point within the Sub… End Sub of a procedure and press F5 to run it, or;
2. In the Run menu, click on Run Macro, or;
3. In the Standard toolbar, there is a green triangle point right. That’s the run button – click it to run the current procedure.

If you’re in the Excel Interface you can:

1. Press ALT + F8 to bring up the Macro dialog box. In the Macro dialog box, select the macro you want to run and click on run.
2. From the Macro dialog box you can also assign a shortcut key combination (e.g. CTRL + SHIFT + C) to a macro. First select the macro, then click on Options, then enter your shortcut key combination and click OK. Back in Excel press your key combination to run the assigned macro.
3. Add the macro to a custom ribbon menu. Right click on an empty space in the ribbon and select “Customize the Ribbon”. In the Excel Options dialog box add a new custom tab for your macro. Select to choose commands from “Macros”. Then add the macro to the new custom tab using the “Add” button.

Note: Remember you can press ALT + F11 to switch between VBA Editor and Excel.

Store your macros in Modules

Now that you have a Personal Macro Workbook, you will want to add macros to it. Place code in here that you will use yourself (and not for distribution to other users).

Use modules to organize your code. Think of code modules as chapters of a book, and your functions and subroutines as paragraphs in each chapter.

To add a Module:

• In the VBA Editor select Insert > Module to add more code modules (shortcut ALT > I > M).

To copy a Module:

• You can copy Modules quickly between workbooks by clicking and dragging from one VBAProject to another.

To remove a Module:

• In the VBA Editor Project Explorer, right click on the module and select “Remove …”
• In the next dialog box, click “No” to remove without exporting the code or click “Yes” to export first and then remove, or cancel to keep the module

Rename your Modules

You can rename your modules to something more descriptive than Module1 (e.g. Mod_Worksheets to store VBA code that helps you work with worksheets).

To rename a module:

1. Click the module in the Project Explorer to select it
2. Go to the Properties Window and click on the name box
3. Type in a new name then press Enter

Also… I like using keyboard shortcuts for tasks I do frequently. If you like shortcuts too, try this one. I think you’ll like it 😊

Here’s the key combination to insert a new module and rename it:

1. Insert new module (ALT > I > M)
2. Rename module (press F4 twice)

#5 – Sample Code – some VBA code gems to keep in your new code vault

» Back to contents

Here are some VBA code snippets for you to stash in your new code vault.

And there are tons more that you can find freely on the web (using Google or another search engine).

When you see “Private Sub” in the code below, this means a user cannot run the macro in the Excel Macro Dialog box. If they press ALT + F8 they will not see any private macros. You can remove the Private to make the macro visible in the Excel Macro Dialog box.

Sort worksheets in alphabetical order (A – Z)

Private Sub SortSheetsAtoZ()
' PURPOSE : Sort worksheets in alphabetical order

'// Declare variables
Dim Counter_1 As Integer
Dim Counter_2 As Integer

'// First For Loop through every sheet
For Counter_1 = 2 To Sheets.Count

    '// Second For Loop through every sheet from 1 to Counter_1 - 1
    For Counter_2 = 1 To Counter_1 - 1

        '// Compare name of current sheet and previous sheet
        If Sheets(Counter_2).Name > Sheets(Counter_1).Name Then
        
            '// Move current before previous if alphabetically correct
            Sheets(Counter_1).Move before:=Sheets(Counter_2)
            
        '// Close If statement
        End If
    
    '// Loop back
    Next Counter_2

'// Loop back
Next Counter_1

End Sub

Sort worksheets in reverse alphabetical order (Z – A)

Private Sub SortSheetsZtoA()
' PURPOSE : Sort worksheets in reverse alphabetical order

'// Declare variables
Dim Counter_1 As Integer
Dim Counter_2 As Integer

'// First For Loop through every sheet
For Counter_1 = 2 To Sheets.Count

    '// Second For Loop through every sheet from 1 to Counter_1 - 1
    For Counter_2 = 1 To Counter_1 - 1

        '// Compare name of current sheet and previous sheet
        If Sheets(Counter_1).Name > Sheets(Counter_2).Name Then
        
            '// Move current before previous if alphabetically correct
            Sheets(Counter_1).Move before:=Sheets(Counter_2)
            
        '// Close If statement
        End If
    
    '// Loop back
    Next Counter_2

'// Loop back
Next Counter_1

End Sub

Synchronize all worksheets in the active workbook to the same view as the active sheet

This subroutine is handy for resetting the view of all worksheets in your workbooks.

Let’s say you have 20 worksheets with different levels of zoom and different cells selected….

… you could manually adjust each worksheet to select A1 and set the zoom to 100%.

Or you could adjust one worksheet to the correct view and then run the macro to sync all the other worksheets to the active worksheet.

It’s so much easier to use the macro than manually adjust each worksheet!

Private Sub SyncWorksheetsToActiveSheet()
' PURPOSE : Synchronize all worksheets to same view as ActiveSheet

'// Check if the User Selected Sheet is a worksheet
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

'// Declare variables
Dim Wks_Selected As Worksheet
Dim Wks As Worksheet
Dim View_Row As Long
Dim View_Col As Long
Dim View_Zoom As Variant
Dim View_Selected As String

'// Set User Selected Sheet in memory
Set Wks_Selected = ActiveSheet

'// Extract view information from Active Sheet
'// Row:  ActiveWindow.ScrollRow
'// Col:  ActiveWindow.ScrollColumn
'// Zoom: ActiveWindow.Zoom
'// Rng:  ActiveWindow.RangeSelection.Address

View_Row = ActiveWindow.ScrollRow
View_Col = ActiveWindow.ScrollColumn
View_Zoom = ActiveWindow.Zoom
View_Selected = ActiveWindow.RangeSelection.Address

'// Loop through all worksheets in active workbook
For Each Wks In ActiveWorkbook.Worksheets

    '// Skip hidden sheets (check using If Then)
    If Wks.Visible = xlSheetVisible Then
    
        '// Activate worksheet
        Wks.Activate
    
        '// Sync view on Active Sheet to Selected Sheet
        ActiveWindow.ScrollRow = View_Row
        ActiveWindow.ScrollColumn = View_Col
        ActiveWindow.Zoom = View_Zoom
        Range(View_Selected).Select
        
    '// Close If statement
    End If
    
'// Loop back to next sheet
Next Wks

'// Restore Original Position
Wks_Selected.Activate

End Sub

Export each worksheet to its own workbook

Private Sub WorksheetsToWorkbooks()
' PUPPOSE : Create a new Workbook for every worksheet in the Active Workbook

'// Declare variables
Dim Wbk As Workbook
Dim Wks As Worksheet

'// Turn off Application Alerts so you don't get
'// alerts when deleting worksheets
Application.DisplayAlerts = False

'// Loop through every worksheet in this workbook
For Each Wks In ActiveWorkbook.Worksheets
    
    '// Create new workbook
    Set Wbk = Workbooks.Add
    
    '// Save workbook in same folder as this workbook
    '// with same name as worksheet
    Wbk.SaveAs ThisWorkbook.Path & "\" & Wks.Name
        
    '// Copy target worksheet to the new workbook
    Wks.Copy before:=Wbk.Worksheets(1)
    
    '// Delete pre-existing worksheet
    Wbk.Worksheets(Wbk.Worksheets.Count).Delete

    '// Close workbook and save changes
    Wbk.Close savechanges:=True

'// Loop back to the next worksheet
Next Wks

'// Turn on Application Alerts so you get alerts
'// when deleting worksheets
Application.DisplayAlerts = False

End Sub

Function to find the last Row, Column or Cell in a worksheet

This function allows you to find the last non-blank row, column or cell in specified range or worksheet.

I recently wrote about different ways to achieve this result… so if you’re interested to learn more, click here to read the tutorial.

Private Function FindLast(SearchType As String, _
                    Optional Str_Worksheet As String, _
                    Optional Str_Range As String)
' PURPOSE : Find last row, column or cell in specified range
'
' PARAMETERS:
' SearchType
'   Row  = last row         returned as Long
'   Col  = last column      returned as Long
'   Cell = last cell        returned as String

Dim Last_Row As Long
Dim Last_Col As Long
Dim Wks_Search As Worksheet
Dim Rng_Search As Range

    On Error GoTo ErrHandler

    'Default to ActiveSheet if Worksheet not specified
    If Str_Worksheet = "" Then
        Set Wks_Search = ActiveSheet
    Else
        Set Wks_Search = Worksheets(Str_Worksheet)
    End If

    'Default to all cells if Range not specified
    If Str_Range = "" Then
        Set Rng_Search = Wks_Search.Cells
    Else
        Set Rng_Search = Wks_Search.Range(Str_Range)
    End If
    
    Select Case SearchType

        Case "Row", "row":      'Find last row with data
            On Error Resume Next
            FindLast = Rng_Search.Find(What:="*", _
                            After:=Rng_Search.Cells(1), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
            On Error GoTo 0
    
        Case "Col", "col":      'Find last column with data
            On Error Resume Next
            FindLast = Rng_Search.Find(What:="*", _
                            After:=Rng_Search.Cells(1), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
            On Error GoTo 0
    
        Case "Cell", "cell":    'Find last cell using last row and column
            On Error Resume Next
            Last_Row = Rng_Search.Find(What:="*", _
                            After:=Rng_Search.Cells(1), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
    
            Last_Col = Rng_Search.Find(What:="*", _
                            After:=Rng_Search.Cells(1), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
    
            FindLast = Wks_Search.Cells(Last_Row, Last_Col).Address(False, False)
                        'Return Address Row, Col as Relative References (not Absolute)
            
            'If Last_Row or Last_Col = 0 then entire sheet is blank, return first cell
            If Err.Number > 0 Then
                FindLast = Rng_Search.Cells(1).Address(False, False)
                        'Return Address Row, Col as Relative References (not Absolute)
                Err.Clear
            End If
            On Error GoTo 0

    End Select
    
Exit Function
    
ErrHandler:

    MsgBox "Error setting the worksheet or range."

End Function

Do you have any VBA code to share?

If you have any favorite VBA code that you think others would benefit from using in their Personal Macro Workbooks… please share in the comments below. Would love to help more people with code that’s useful 😊

#6 – Backups – how to save copies for safe keeping

» Back to contents

The Personal Macro workbook is stored in your XLSTART folder (see section #3 – Location – where is it stored?) and there is only one version at any given time.

It’s a good idea to backup the workbook in another location in case you accidentally overwrite some code and lose it.

How to backup your Personal Macro Workbook

You can do that by manually copying the file from the XLSTART folder to a backup folder of your choice.

Or you can run the following macro “Save_Backup”.

Sub Save_Backup()
' PURPOSE : Save a copy of this workbook with date appended
'           Drive:\path\filename-yyyymmdd.xlsb
    
    ThisWorkbook.SaveCopyAs Filename:= _
        "C:\Backups\Excel Files\" & _
        Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", , vbTextCompare) - 1) & _
        "-" & Format(Date, "yyyymmdd") & ".xlsb"

End Sub

This saves the Personal Macro Workbook to a folder of your specification and appends the date in yyyymmdd format.

Replace the folder path “C:\Backups\Excel Files\” with your own backup folder path.

How to restore your Personal Macro Workbook

To restore a backup:

• Go to the XLSTART folder and delete the existing Personal Macro Workbook
• Copy the backup workbook into the XLSTART folder
• Rename the backup workbook to “PERSONAL.XLSB”

#7 – Recap – remember what you learned

» Back to contents

Let’s review the main points we covered today. This will help you understand and remember it for longer. Any questions just let me know in the comments section.

Personal Macro Workbook

• The Personal Macro Workbook is a hidden workbook that opens automatically when you start Excel.
• Think of it as your personal code vault that is only accessible to you on your own computer.
• Any VBA code you keep in it will be accessible in any workbook that’s open in Excel.
• It’s a great place to store code samples (also known as code snippets) that you can copy and paste into your other macros.

Get your own

• When you start using Excel with a fresh installation, there is no Personal Macro Workbook.
• To get your own Personal Macro Workbook follow these steps:
  • Step 1 – Record a dummy macro in “Personal Macro Workbook”
  • Step 2 – Check the Personal Macro Workbook exists
  • Step 3 – Delete the dummy macro
  • Step 4 – Save the Personal Macro Workbook
  • Step 5 – (Optional) Change the VBA Project name

Location

• If the Personal Macro Workbook exists, you can find it in the Excel startup folder.
• C:\Users\Name\AppData\Roaming\Microsoft\Excel\XLSTART
• AppData is a hidden folder – don’t panic if you can’t find it!
• To make AppData visible, use the Folder Options dialog box in Windows Explorer to show hidden files, folders and drives

Storing and Running macros

• You organize your macros in code modules.
• Think of the Personal Macro Workbook as a book, with code modules being chapters of the book and procedures being paragraphs in each chapter.
• There are many ways to run the macros in your Personal Macro Workbook. Here are ways we covered:
  • (VBA Editor) Hit F5 to run the current macro.
  • (VBA Editor) In the Run menu, click on Run Macro.
  • (VBA Editor) In the Standard toolbar, click on the run button with green triangle pointing right.
  • (Excel) Press ALT + F8 to bring up the Macro dialog box and choose a macro to run.
  • (Excel) Use the Macro dialog box to assign a shortcut key combination.
  • (Excel) Add macro to a custom ribbon menu.

Sample Code

• I shared some sample VBA code you can put in your new code vault. Here’s a list:
  • Sort worksheets in alphabetical order (A – Z)
  • Sort worksheets in reverse alphabetical order (Z – A)
  • Synchronize all worksheets in the active workbook to the same view as the active sheet
  • Export each worksheet to its own workbook
  • Function to find the last Row, Column or Cell in a worksheet
• Feel free to share any VBA code you think is useful in the comments below.

Backups

• To create a backup you can manually copy the PERSONAL.XLSB file from the XLSTART folder to a backup folder of your choice.

You can also run the macro “Save_Backup” to automatically save to a folder of your choice with the date appended.

Please leave a comment below if you have any questions or need help with your Personal Macro Workbook. I’ll try my best to help! 🙂

And if you have any favorite VBA code that you think others would benefit from using in their Personal Macro Workbooks… please share in the comments below.

#8 – References – further reading to learn more

» Back to contents

Here are references you might find useful.

• Accounting Macros – for automating simple tasks
• Chandoo.org (What would James Bond have in his Personal Macro Workbook?)
• Chandoo.org (Secret Agent KV’s Chops… what’s in his personal macro workbook?)
• Cybertext Consulting (Can’t see the AppData folder?)
• Excel Campus (How to create a Personal Macro Workbook video series)
• Make Use Of (What’s the Difference Between AppData Roaming and Local?)
• Microsoft Support (Copy your macros to a Personal Macro Workbook)
• MrExcel forum (Macro to save backup copy of this workbook with date appended)
• MrExcel forum (How do I import my personal macro workbook to another computer?)
• NHS Excel (What to do with the Personal Macro Workbook + VBA Samples)
• Reddit (What are some of the best macros you have saved in your personal workbook?)
• Ron de Bruin (Excel Automation – create and use a PERSONAL file for my VBA code)