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
Are You Turning the Clocks Back (or Forward)?
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.
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.
Video 2: Where the Personal Macro Workbook file is stored on your computer and how to view it.
Video 3: How to customize the Excel Ribbon and Quick Access Toolbar to add your own custom macro buttons.
Video 4: How to make a Yes/No message box (pop-up) window appear before the macro runs, critical to preventing disaster…
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.
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
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
Turkey
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
Turkey
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.
i like the vlookup assistant
Looking forward to using the “create all connections” macro. Thank you so much for your series on macros and Power Query–it’s really upped my Excel game.