How to Create a Personal Macro Workbook (Video Series)

Bottom line: This video series explains what the Personal Macro Workbook is, how to create it, and why you should use one.  You will also learn how to add custom macro buttons to the Excel Ribbon, and how to make a Yes/No pop-up window appear before a macro runs.

Skill level: Intermediate

Create the Personal Macro Workbook and Add Buttons to the Ribbon

Are You Turning the Clocks Back (or Forward)?

Daylight Sleepy Time

If you live in a country that observes Daylight Savings Time (over 70% of countries do), then this is the time of the year that the clock shifts an hour.

I live in the northern hemisphere (U.S.), and we lost an hour this past weekend.  Technically the clock shifts in the middle of the night while you are sleeping, but I think that you really lose that hour over the course of an entire week with every yawn and eye rub you do. 😉

I don't know about you, but the “Fall back” time change always makes me a little sleepier for a few days.

On a side note, my wife and I use a wake-up light (amazon link) instead of an alarm clock, and wake up in a much better mood these days.  I still yawn in the morning, but it is a lot less grumpy. 🙂  If you get up before the sunrise then you might want to check it out.

The Personal Macro Workbook can Save You Hours!

The time change has inspired me to help you save an hour out of every work week.

How?  With the Personal Macro Workbook.

You probably already know that macros can help automate routine Excel tasks to save you time.  This is one feature that makes Excel awesome!

The Personal Macro Workbook is just an Excel workbook that stores all your macros in one place.

The nice part is that it opens in the background every time you open Excel.  This makes it easy to run your macros at any time.

Excel Personal Macro Workbook Tool belt or Purse Quote

I like to think of it as your Excel tool belt.  Ladies, you can think of it as your Excel purse.  🙂  The Personal Macro Workbook is always at your side, ready to assist you with common daily tasks.

These are usually simple tasks like refreshing all the pivot tables or creating a list of all the sheets in the workbook.  But you can store any macro, function, or userform in your personal macro workbook.

Video Series on the Personal Macro Workbook

I have put together a video series that explains everything you need to know to get started with your own Personal Macro Workbook.

I even explain how to record your first macro, and I have additional free training videos if you are new to macros and VBA.

This 4-part video series includes:

Video 1: How to create the Personal Macro Workbook, and why you should use it.

Watch on YouTube & Subscribe to our Channel

Video 2: Where the Personal Macro Workbook file is stored on your computer and how to view it.

Watch on YouTube & Subscribe to our Channel

Video 3: How to customize the Excel Ribbon and Quick Access Toolbar to add your own custom macro buttons.

Watch on YouTube & Subscribe to our Channel

Video 4: How to make a Yes/No message box (pop-up) window appear before the macro runs, critical to preventing disaster…

Watch on YouTube & Subscribe to our Channel

In the videos I mention my free training series on Macros & VBA.  That video series covers everything to get you started writing your own macros and automating tasks in Excel.

Click here to signup for the free training on Macros & VBA

Download the File

You can download the file I used in the videos below.  This file contains the code for the Yes/No Message Box and a few other macros.

What Macros Are In Your Personal Macro Workbook?

If you are already using a personal macro workbook, then you might pick up some tips from the video series.

However, I want to know what macros you are using every day.  Please leave a comment below with a description, or the code for the macros you can't live without.

If you are looking for a macro that can automate a common task, but not sure how to create it, leave a comment below as well.  My hope is that this page can be a place where we share our macros and learn from each other.

Click here to leave a comment with your favorite macro, or one you want

Note: When leaving a comment below with your code, you can paste the code in the comment box and wrap it the <pre>pasted VBA code</pre> tags.  This will keep all the indentations and line breaks from the VB Editor.


Your email address will not be published. Required fields are marked *

  • Explains well how to create Personal Macro & add to Toolbar.
    What I wanted to do is pick up amounts with vlookup from closed workbooks on various sub-folders and add the amounts together in a final workbook that we are working on.

  • very new to excel – looking to copy all data from one tab in excel sheet to another tab in another file. I then need the data refreshed to refresh my pivot tables.
    Then I want to save it and open a powerpoint file already set to copy data from the excel file

  • Hey,

    I would like to run a macro to calculate retail margin, very similar to your percentage difference macro but with the below formula instead


    And also for non VAT items


    Any help would be great!

  • Yes/No checks are needed so often that I wrote a little function for it…

    ‘Sugar over the MsgBox machinery (with props to window.confirm)

    Public Function Confirm(ByVal Prompt As String, Optional ByVal Caption As String = “Confirm Action”) As Boolean
    Confirm = (vbYes = MsgBox(Prompt, vbYesNo, Caption))
    End Function

    Public Sub TestConfirm()
    Debug.Print Confirm(“Do you really want to run this?”)
    End Sub

  • hi Jon,

    I have a macro that works but is clunky and not as fast as i’d like it to be. it is an automatic RowHiding/UnHiding & ColumnHiding/UnHiding Macro – based on criteria in Columns A and B for RowHiding, and Row(s) 1 or 2 for ColumnHiding.

    I hope to find a macro to Hide/UnHide multiple specified worksheets in a multi-Worksheet workbook

    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘Conditional RowHiding-of-1st-Column’s Rows
    ‘For Worksheet IS-BS-CF-SU Proformas
    ‘Description: this macro will loop through the 1st column and
    ‘hide the row(s) if the cell has the value(i.e.”H”) in cell(1,1), or A1
    Dim v As Variant, i As Long
    If Intersect(Target, Range(“A1″)) Is Nothing Then GoTo Line2
    Application.ScreenUpdating = False
    UsedRange.Rows.Hidden = False
    If IsEmpty(Cells(1, 1)) Then GoTo Line2
    v = Cells(1, 1).Value
    For i = 11 To Cells(65536, 1).End(xlUp).Row
    If Cells(i, 1) = v Then Rows(i).Hidden = True
    Next i

    GoTo Line2

    ‘Conditional RowHiding-of-2nd-Column’s Rows
    ‘For Worksheet IS-BS-CF-SU Proformas
    ‘Description: this macro will loop through the 2ndt column and
    ‘hide the row(s) if the cell has the value(i.e.”H”) in cell(1,2), or B1

    If Intersect(Target, Range(“B1”)) Is Nothing Then GoTo Line3
    Application.ScreenUpdating = False
    UsedRange.Rows.Hidden = False
    If IsEmpty(Cells(1, 2)) Then GoTo Line3
    v = Cells(1, 2).Value
    For i = 11 To Cells(65536, 2).End(xlUp).Row
    If Cells(i, 2) = v Then Rows(i).Hidden = True
    Next i

    GoTo Line3

    ‘Conditional ColumnHiding-of-1st-Row’s Columns
    ‘Description: this macro will loop through the 1st row and
    ‘hide the column(s) if the cell has the value (i.e. “HC”) in cell(1,4), or D1)
    If Intersect(Target, Range(“D1”)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    UsedRange.EntireColumn.Hidden = False
    If IsEmpty(Cells(1, 4)) Then Exit Sub
    v = Cells(1, 4).Value
    For i = 11 To Cells(1, 16383).End(xlToLeft).Column
    If Cells(1, i) = v Then Columns(i).Hidden = True
    Next i

    End Sub

  • I created My Macros tab, but the add_3_Sheets button will not run. I receive the error cannot run the macro Add_3_Sheets. The macro may not be available in this workbook or all are disabled. I can run the macro from the developer tab

  • merhaba benim bir fiyat listem var yaklaşık 1000 müşterime dağıtmışım
    online tek bilgisayarda nasıl fiyat listemi göncelerim
    vba kod üzerinde yapabiliri miyim yardımcı olabilirmisin veya yaptığınız bir çalışma varmı

    online çalışma
    iyi çalışmalar diler

    Google Translate

    hello i have a price list i have distributed to about 1000 customers
    how do i send my price list on one computer online
    Can I do it on vba code? can you help or is there any work you have done?

    online study
    wish you good work

  • Jon, I’ve followed and learned from you for years now. Your training videos are great. Thanks for that!
    I am running into a problem with my Personal.xlsb workbook where it does not open when I run excel and I can’t use the macros stored unless I open the workbook manually.
    Is there a way to make the personal.xlsb workbook open on startup? Please help.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter