How to Create a Personal Macro Workbook (Video Series) - Excel Campus
53

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 TimeIf 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 LikeYouTube 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 LikeYouTube 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 LikeYouTube 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 (18.9 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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 53 comments
Mickael - October 24, 2017

Jon,

You’re the man, thank you so much!

Tell me please, what should we do when the Personal macro file opens as a read-only? It happens to me very often, and it prevents me from saving/building onto the existing personal macro.

Best regards,
Mickael

Reply
Khalid - October 21, 2017

thanks Jon , your video gave me idea to change my micro that I used to create new sheet from the template as below:

Sub NEEEW()

‘ NEEEW Macro


Sheets(“10-TEMPLETE”).Select
Sheets(“10-TEMPLETE”).Copy Before:=ActiveSheet <– before I used Sheet(number)
Sheets("10-TEMPLETE (2)").Select
Sheets("10-TEMPLETE (2)").Name = "10-0XX"
Range("O16").Select
ActiveSheet.Shapes.Range(Array("Rectangle 4")).Select
Selection.Delete
Sheets("10-0XX").Select
With ActiveWorkbook.Sheets("10-0XX").Tab
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
End With
Range("C4:G4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("C4:G4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L12").Select
End Sub

but I need to know if I can make the micro rename the created new sheet with the sequence number ( like gave the created sheet name 01-009 after sheet name 01-008)
any suggestion ?

Reply
Bill - May 30, 2017

I have a small Stump Grinding company and I keep my books in Excel. In my record books (Excel) I have one column labeled “Expense Summary” and another column labeled “Expenses”. In Expense Summary is a breakout of the categories my CPA wants to see the info (Labor; Parts, Supplies; etc.) when he does my taxes. The Expense column simply shows date, Customer, Expense, and explanation of cost/receipt. Both “Expense column” and “Expense Summary columns” information contained in the first three columns is the same. The change comes when I fill in the Expense Summary column when I have to identify the category in lieu of explanation of cost. I need develop a macro that will automatically insert info (date, Customer, Expense) in Expense Summary when it is typed into the Expense column. That would sure make my book keeping a lot less redundant. Any suggestions?

Reply
Florence Lorenzo - May 24, 2017

Hi Jon,

I have created different modules for each different workbooks. What command should I enter into my excel macro to automatically activate or open each Excel workbook and run each macro? I usually open the workbook and make sure that workbook is the current active workbook then I go back to my macros to run the module.

Thanks for your help.

-Florence Lorenzo

Reply
Florence Lorenzo - May 24, 2017

Hi Jon,

I have a Personal Macro Workbook that contains multiple modules. When I open each module, some modules are empty. How can I delete all empty modules without opening each module? Also, is there a way to backup my Personal Macro Workbook? If yes, how can I restore back the previous copy of my Personal Macro workbook if I accidentally delete a module from my current Personal workbook?

Reply
Craig - May 17, 2017

I’m much later on this than others (no jokes about New Zealand please)
I’ve tried to create a macro (using “Record”) to pick a couple of our predefined print settings e.g. double sided, hole punched, stapled or just plain single sided.
But when I hit stop recording it doesn’t seem to have picked up what I have done.
s it possible to set a macro to achieve what I’m doing? We have three that we use regularly, and its a pain going through a series of clicks each time.

Reply
Raphael - December 15, 2016

How to insert data from one cell (with hyperlink to another cell), into another cell, in another sheet, but same workbook, when activated?

Reply
Yanis - September 5, 2016

hey there! copied the Copy_File_Path into my personal macro workbook, but I’m getting error “User-defined type not defined” when trying to run it.. Any ideas what I might be doing wrong?

Reply
    Jon Acampora - September 7, 2016

    Hi Yanis,

    Great question! You will need to add a reference to the Forms 2.0 Object library. This can be done from the Tools > References menu in the VB Editor. First, select the Personal macro workbook in the Project Explorer window on the left side. Then follow the steps on the screenshot below.

    Reference Forms 2.0 Object Library in VB Editor

    Reply
Tristan - July 14, 2016

Thank you for quality of your video, and encouraging us to do our own tools belt.

Reply
Carlos Guillermo - July 13, 2016

Hi jon,
it was very nice tutorial very nice job I am you fan for VBA in excel
do you have a book that I can get and where I can buy it and videos too.

Thank you

Carlos

Reply
Kammy - June 8, 2016

Hi Jon,

This is fantastic. I have to send emails on a weekly basis and I have been doing it through multiple documents and multiple macros. Having them all in one spot is incredible and so user friendly.

Thanks!!

Reply
GEORGE THOBOIS - April 27, 2016

This 1st video was very clearly presented and easy to follow along. Great explanations!

Reply
m-b - April 1, 2016

I have two main ones which I use a lot during the day:
– One to put two workbooks side by side on two screens;
– One to add a new window for the current workbook and put it on the second screen.
These save a lot of time compared to manually moving around windows.

Reply
Albert Smith - March 28, 2016

I have a series of bills to pay @ the end & the beginning of the month. The end of the month consist of 13 bills & the beginning 9 bills. 6 of the bills are automated taken out of my bills. I want to automate my bills & maneuver until I get the amounts that I want to pay. After I get the amount then mark all the bills in red. I made a macro recording to change all the bills back to black.

Thank you

Reply
Dave Bruns - March 21, 2016

Nice work, Jon! I like the tip for finding out the location of the personal workbook.

Reply
Cindy Baker - March 18, 2016

I am just now getting into using macros. Like Melanie, I didn’t create any, just used the code.
List Sheets
Add and rename worksheet

I would like a macro that will copy and rename a worksheet, I assume it would work similar to the add and rename macro. I need to figure out the correct code. Any help would be appreciate:)

As always….loved the videos Jon!

Reply
    Jon Acampora - March 24, 2016

    Thanks Cindy! The code to copy and rename a sheet is going to be similar. It does depend a bit on what you want to rename the copy. Do you want to give it a specific name, or prompt the user to input a name?

    My Tab Hound add-in has a button for this feature where you can copy and rename a sheet, and then an input box appears that allows you to specify the new sheet name. Is that the functionality you are looking for? Thanks!

    Reply
Pablo Baez - March 17, 2016

Hi John,
Thanks for putting the time and effort to create the VBA videos. I know you have been working on this for a while.

A handy macro is to flip signs:

Sub Change_Signs()
    For Each cell In Selection
        If IsEmpty(cell.Value) Or cell.HasFormula Then
            'go to next cell
        ElseIf IsNumeric(cell.Value) Then
            cell.Value = cell.Value * -1
        End If
    Next cell
End Sub

Recently Phil Treacy (Mr. Mynda) published this macro using Evaluate. As he said it has room to improve to skip formulas & blank cells, like the one above:

Sub ChangeSign()
    Selection = Evaluate(Selection.Address & "*-1")
End Sub

Thanks,
Pablo

Reply
Stewart - March 17, 2016

I have a number of macros which I forget to use so may have a look at creating the ribbon

The one I often use is to change the text case


‘Macro to change case in a cell (From Inside Excel Jan 98)

Sub ChangeCase()
Dim entry
entry = ActiveCell.Value
If entry = LCase(entry) Then
ActiveCell.Value = Application.Proper(entry)
ElseIf entry = Application.Proper(entry) Then
ActiveCell.Value = UCase(entry)
Else: ActiveCell.Value = LCase(entry)
End If
End Sub

I tend to use this with a keyboard short cut.

I do tend to have the file visible all the time has it has some useful links to various workbooks I use a lot. I also have some currency formats set which it is easier to copy and paste formats rather than using format numbers.

Stewart

Reply
    Mohamed Chakroun - June 14, 2016

    Hi evry body
    How to make this macro working with a specific selection not only for the activecell

    Thank you

    Reply
Alfred - March 17, 2016

Hi Jon,
I very much appreciate the way you do your teaching, your voice, the relaxed way of explaining things, and – for an US American … 😉 – your modest (in the best sense of the word) presentation. Thanks a lot.
In MHO, there’s one simple, but nonetheless important fact missing in your first video:
the Personal Macro Workbook does not exist on your pc from the very beginning. You have to “create” it as you did in your video. In my classes there are people not to understand at all why we should go through that – basically – silly process. It would have been so helpful if MS had added a button or a menu option to the VBA Editor. Or, is there one I ignore? Oh yes! There’s another one I miss a lot –> “Exporting the Personal Macro Workbook”, in case you want to share the PMW! And why not allow for our team to share the PMW on a group drive?

Reply
Michael - March 16, 2016

Right now my dream macro would be a simple copy and paste filtered data from 1 spreadsheet into 6 separate spreadsheets with 5 workbook tabs. Just had this task dropped on my lap this morning and it took me an hour and I’ll be needing to do this every week. 😛

Loving paste buddy with the shortcut for paste values and formulas is already a time saver. Paste visible cells has been wonderful too.

Reply
GMF - March 16, 2016

I have a lot of helper macros, some simple, many with associated user forms. Some are in the quick link toolbar, some have hotkeys.

Simple:
– Center across selection (why this isn’t a single button already is beyond me)
– Turn off zeros
– Set footers giving copyright and confidentiality notices
– Set margins and page scaling
– Create a static or dynamic range name for selected cells

Forms:
(I have to thank every Excel website out there for the bits and pieces that have gone into these – couldn’t have done them alone.)
– Insert a color-coded status into a cell
– Insert notes into a cell, saving previous entries and putting a timestamp before the new entry
– For charts, change line colors, widths, markers, data labels, axis values etc. (this one grows over time but if I want to give points in a column series gradients of the same color instead of Excel’s default it’s really nice. I can easily set a data label for a single point such as the last one for labeling line chart series, things like that.) It could use some professional debugging but I know where all the gotchas are. It also converts standard 2-series graphs into either slopegraphs or 2-panel column charts.
– Macro for documenting procedures and functions. This is another one that gets tweaked over time, but it creates a header for the selected module, procedure, function that tells what calls it, what it calls, DLLs used, modification history. I really appreciate it 6 months later when I can’t remember why I made a change.
– Calendar! Since Excel 2013 64-bit lost the handy calendar control I had to build my own. Doesn’t handle multiple dates, but I find I rarely need that capability.

Reply
    Jon Acampora - March 16, 2016

    Thanks GMF! I completely agree with you on the center across selection! Merge cells can cause so many issues with VBA. I also have to thank every Excel website and forum out there as well. I wouldn’t be here if it wasn’t for them. 🙂 Have a good one!

    Reply
      GMF - March 17, 2016

      I am curious as to whether your readers have seen restrictions on macro-enabled worksheets. As a consultant I sometimes encountered clients with security policies that prevented the sharing of .xlsm worksheets thru email or shared drives. That accelerated my shift from .xlsm to keeping my macros in the personal workbook. It also made me scramble for (often array) formula equivalents to macros… convoluted but effective.

      Jeff Weir’s tip about add-ins rather than PWB, at least for UDFs, is a good one, but those same security-conscious companies made installing add-ins onerous as well. I wouldn’t mind a quick column or two on setting those up, and how to make it a top-level ribbon item or an option in the existing add-in tab.

      I’d also be curious if your readers prefer keyboard shortcuts or ribbon access for their macros. It’s probably opening a can of worms along the lines of which way to hang the toilet paper roll, but I’m usually a keyboard or right-click menu guy. If I’ve moved my cursor to a specific cell to perform an action, why move the darn thing again to click on a ribbon when I can hit a couple of keys? But then again, I still have to fight against the Excel 2003 shortcuts.

      Not to mention all the VB6 code I’ve ported to VBA. Your readers are probably too young to appreciate that!

      Reply
        Heather R - March 17, 2016

        GMF – I know exactly what you mean, I’m working in one of those companies where most co-workers don’t know what macros are, and they are scared of the security warning.

        For that reason, I’m working on building an addin to handle the monotonous tasks that take up so much time.

        Right now, it looks like I’ll need some different ones, for different business units. But hopefully it will be worth it for everyone in the long run 🙂

        And yes, I prefer right-click options myself as well.

        Reply
Bob Kaplan - March 16, 2016

I have just 2 macros in my Personal Workbook. One is to change the direction after pressing enter to “right” and the other changes it to “down”. (Yes, I could do it in one macro, but I got lazy and just recorded them both.)

These seem extremely lame and basic compared to what other I’m seeing here. I do need to develop my workbook more as I get time. It is an amazing tool!

Reply
    Jon Acampora - March 16, 2016

    Hi Bob,
    Lame and basic are exactly what I like because these are the boring tasks that we don’t want to waste time with. 🙂

    I saw this video from my friend Dave Bruns at ExcelJet that mentioned this tip on changing the Enter key direction in the Excel Options menu. I know you asked this before, and I had completely forgotten about this option. Here is a screenshot of the Excel Options menu where you can find it.

    Excel Enter Key Direction Excel Options Menu

    Reply
Heather R - March 16, 2016

Some of mine are simple – others more involved, depending on what I’m doing. These are the few that I use often enough to have put into buttons on my Ribbon (in their own tab). Anything else is just listed in the macros box on the Developer tab.

Unhide ALL sheets (even the very hidden ones) – thanks Jon for that lesson! 🙂

Kill all styles (for those times when I get something thousands of extra styles that are unused, but still live in the workbook due to merging data from different sheets)

Then I have three that format specific forms that I use regularly from our database – so these macros process them into a ‘pretty’ version for sending to management or our sales people.

Paste Special ALL (to remove all formulas from every cell – although I do need to update that one because it uses a copy/paste method on every cell in each worksheet)

Delete Hidden Rows and Columns (I have a program that builds an Excel file with multiple hidden rows and columns that are not needed for final output)

Reply
Paul - March 16, 2016

John,
I have a number of Excel tools that users use to generate a list of options. Users complain that the Excel output for these lists is cumbersome and they would like me to provide them with a WORD template where they can:
* Open the WORD template
* See an input window asking “How many lists do you have?”
* They will enter a number (say 2 for simplicity but it could be more)
* WORD would open a second window asking the user to browse to the Excel workbook containing the 1st list.
* WORD macro would go to that Excel workbook, copy the list (Steet 1, cells A1 thru A?? (depending on how long the list is, or use a LOOP function)) and paste “values” into Word.
* WORD would re-open that second window and ask the user to browse to the Excel workbook containing the 2nd list.
* WORD macro would go to that Excel workbook, copy the list (Steet 1, cells A1 thru A?? (depending on how long the list is) and paste “values” into Word.
* Repeat based on the number of lists there are.
* Close all Excel workbooks used during this process.
* In WORD, ask user to enter a file name and browse to where they want it saved.
* Save it as a Word DOC (not template)

Do you have code (parts or whole) that would do this?
If pulling from Excel into Word won’t work another option would be to start in the Excel and PUSH the data into Word?

Reply
Bruce L - March 16, 2016

Spend my day wrangling lots of adhoc data and couldn’t live without this.

Sub ConvertSelectionToValues()
'Converts selection to values.
'Equivalent of copy..paste special..values, except a little bit more
'intelligent, can cope with multiple selection areas and - most
'importantly - can be assigned to a single shortcut key.

Dim s As Range
Dim c As Range
Dim a As Range
    
    If TypeName(Selection) = "Range" Then
    
        Set s = Selection
        
        If s.Columns.Count = s.Parent.Columns.Count And s.Rows.Count = s.Parent.Rows.Count Then
            'whole sheet selected - resize selection to used range
            Set s = s.Parent.UsedRange
        End If
        
        'loop through areas in selection and paste values in
        'visible cells only (allows use on filtered ranges).
        For Each a In s.Areas
        
            'First need to test if a is just a single cell, otherwise
            'SpecialCells(xlCellTypeVisible) in the subsequent test will
            'return all visible cells in the worksheet, which we don't want.
            If a.Cells.Count = 1 Then
                a.Formula = a.Value
                
            ElseIf a.Cells.Count = a.SpecialCells(xlCellTypeVisible).Count Then
                'no hidden cells, can just do all at once
                a.Formula = a.Value
                
            Else
                'has hidden cells - need to loop through visible cells.
                
                'Make a note of current calc mode then put XL in manual
                'calc mode to speed things up a bit.
                ocm = Application.Calculation
                Application.Calculation = xlCalculationManual
                
                'loop through visible cells and 'paste values'
                For Each c In a.SpecialCells(xlCellTypeVisible).Cells
                    c.Formula = c.Value
                Next c
                
                'Restore previous calculation mode
                Application.Calculation = ocm
                
            End If
            
        Next a
        
    End If
    
End Sub

Reply
    Bruce L - March 16, 2016

    Hmm – I used pre tags in that comment, but it doesn’t seem to have picked them up. Sorry!

    Reply
      Jon Acampora - March 16, 2016

      Thanks for the comment Bruce! No worries, I updated the comment with the tags.

      I remember the first time I learned about the Areas property. It really comes in handy when you are working with or looping through multiple ranges.

      Thanks again! 🙂

      Reply
Melanie Beck - March 16, 2016

I have several! I didn’t create any of these; I just used the code.

List all worksheet tabs
List all file names in a folder as hyperlinks – very handy
Close a workbook after a time limit is reached.

Just a few. I have never been good at writing code to make macros. But, I know how to record and copy/paste code to use. I am getting pretty good at editing simple code to make it work for my purposes too.

I like the yes/no popup. I’m going to use that one.

Thanks.

Reply
Sandy Hodgson - March 16, 2016

I don’t use this macro every day, but anytime that I accidentally hit the F1 button instead of the F2 button. It is very simple, but more useful then physically removing the F1 key as a former co-worker used to do for the same reason ;).

Sub Disable_F1()
    'This macro disables the F1 key.
    Application.OnKey "{F1}", ""
    MsgBox "F1 key has been disabled."
End Sub

Reply
INDZARA - March 16, 2016

I have never used a personal macro workbook. But this post has made me curious. Thanks for sharing, Jon.

Reply
Vincent Hahn - March 16, 2016

Nice article mate.

I usually store – for now – my UDFs (such as MINIF or MAXIF which aren’t directly available for me as Excel 2013 user) in the personal macro workbook. It’s a bit annoying to reference the Personal macro workbook in the function call though 🙁

Reply
    Jeff Weir - March 16, 2016

    It is for this reason that I save my UDFs and other things that would otherwise go in the PMW to an add-in file. You don’ don’t use the PMW, but instead save all my code

    Reply
    Jeff Weir - March 16, 2016

    It is for this reason that I save my UDFs and other things that would otherwise go in the PMW to an add-in file. You don’t need to qualify your UDFs with “Personal.xlsb”! when you do this. Otherwise, the process is pretty much identical to saving stuff in the PMW. So in short, ditch the PMW and simply use an Add-In file instead.

    Reply
Jon Acampora - March 15, 2016

Here is a quick macro to refresh all the pivot tables in the active workbook. This macro actually refreshes the pivot caches that hold the source data.

Sub Refresh_All_Pivots()

    For i = 1 To ActiveWorkbook.PivotCaches.Count
        ActiveWorkbook.PivotCaches(i).Refresh
    Next i

End Sub

I used the pre tags when pasting the code above from the VB Editor. This shows the indenting in the code.

Reply
    istivan - March 16, 2016

    A bit more dangerous (e.g. if you have data connections to other workbooks/databases/etc it might take long). But if you only have local pivot tables (i.e. no data connections) this will work:

    ActiveWorkbook.RefreshAll

    Reply
      Jon Acampora - March 16, 2016

      Thanks Istivan! Great suggestion! That line of code will also refresh your Power Query queries. It is basically the same as pressing the Refresh All button on the Data tab of the ribbon.

      If my workbook does not contain any external data connections or Power Query queries, then I typically use ActiveWorkbook.RefreshAll to refresh all the pivot tables.

      Thanks agian and have a good one! 🙂

      Reply
    Alicia - March 20, 2016

    I never thought about this! I have just started using Pivot Tables in my “Master File” and now can refresh all at once. Thank You!

    Reply

Leave a Reply: