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 and give it a thumbs up.
YouTube Thumbs Up Like
YouTube Subscribe Logo Excel Campus

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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

Watch on YouTube and give it a thumbs up.
YouTube Thumbs Up Like
YouTube Subscribe Logo Excel Campus

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 and give it a thumbs up.
YouTube Thumbs Up Like
YouTube Subscribe Logo Excel Campus

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.

PERSONAL - Excel Campus.xlsb (22.4 KB)

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.

  • 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.
    Thanks,

  • 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

    =((Retail/1.2)-Cost)/(Retail/12))

    And also for non VAT items

    =((Retail-cost)/Retail))

    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
    Line2:

    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)
    Line3:
    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

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    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

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >