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 (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.

68 comments

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

  • Hello Jon, it’s a pleasure to say hello

    Jon, I did not find a “macro” for “Automatic Pivot Table Number Format”. Personally I use this macro constantly, that’s why I would really appreciate it if you can help me with some information to create it …

    Best regards

    Sincerely,

    Jeffry (jeffry.duran@gmail.com)

  • Hi. I have a formula in one cell in Workbook_B. This formula basically copies data from another Workbook_A. Lets say it copies from cell A5 in Workbook_A to cell A5 in Workbook_B. Lets say cell A5 represents a price in May.

    The following month there is new data in Workbook_A (lets say a new price in June). This is located in cell A6.

    I need to update cell A6 in Workbook_B to reflect this change.

    Here is the issue. There is already temporary hand keyed June data in cell A6 in Workbook_B. This needs to be replaced by the new June data in A6 in Workbook_A.

    Also, I need to delete cell A5 in Workbook_B after this procedure (i.e. when copy of new data from cell A6 in Workbook_A has been set to A6 in Workbook_B).

    Each month this process is repeated using a macro. So the following month A7 from Workbook_A needs to be copied to Workbook_B and A6 in Workbook_B needs to be deleted and so on…can you help?

  • Hey Jon,

    I downloaded your file “PERSONAL – Excel Campus.xlsb”

    after I opened it, my own macros were gone missing. Can you please advise what happened?

    • Hi Muhammad,

      I see what was happening. The View was set to Hidden for the workbook, and it was likely opening in a new instance of Excel. That is why you might not have seen your personal macro workbook and any other files you had open in the VB Editor.

      I have changed the file in the downloads section above to open in a visible state (View tab > Unhide). You should now see the workbook when you open it.

      I hope that helps.

  • Hey Jon, I have started my personal macro workbook and have gone crazy adding buttons. One questions though, why is it that when you add a button to the custom ribbon, it only runs one sub in a module?

    • Hi Robert,
      The ribbon button will be assigned to one macro and only run that macro. However, you can call other macros within the macro that the button runs. You can just add the macro names as lines of code in your macro to call them. You can also use the Call statement to call the macros. Here is an example.

       Sub Macro_Assigned_To_Ribbon_Button()
      
      Call My_Other_Macro_1
      Call My_Other_Macro_2
      
      'Other code can be placed before or after those call statements.
      'The code runs in order and will run the called macro completely, then
      'jump back to this macro and continuing running it.
      
      End Sub

      I hope that helps.

  • Hi Jon,

    Great set of teaching videos!
    One question – I remember back in the day (Excel 2003 or Excel 2007) we had an editor to custom design our own buttons to attach to macros on the ribbon. This seems to have now disappeared and we appear to be limited to using the provided button set. Have you found any way to generate custom buttons for running macros?

    Thanks
    John

    • Hi John,

      Yes, that was Excel 2003. The new ribbon in Excel 2007 and beyond only has a limited icon set for the buttons. Unfortunately you cannot customize this and I don’t know how they came up with the icons in that short list.

      If you want to fully customize the ribbon buttons with your own images then you can achieve that with Ribbon XML code. I have step-by-step training on customizing the ribbon like that in my VBA Pro Course.

      I hope that helps. Thanks!

  • How do I rename my modules in the VBA editor from “Module 1” to a more meaningful name. I noticed that you have names like “m_AppSize”, “m_Sheets”, etc.

    Thanks,
    Rhonda

    • Hi Rhonda,
      when you select the module you want to rename go to the properties window below and change the name tag. You will see the name of the module has changed into whatever you typed.
      kind regards, Diederik (NL)

  • Jon,
    I found this on stack overflow which has solved my problem

    If you add a UserForm to your project, the library will get automatically added. If you don’t need the UserForm, you can always delete it later.

    Added by somebody called Jon

    Steve

  • Jon,
    Thanks for the videos. I tried to use the Copy_File_Path macro and had the problem described above by Yanis (Sept 2016). When I go into the references dialogue box I cannot find Microsoft Forms 2.0 Object Library. I am running Excel 2016 64bit on Windows 10.
    Any suggestions why this might be the case ?

    Steve

  • 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

    • Hi Mickael,

      That typically happens when you open a second instance of Excel. You can just click the Cancel button if you are opening multiple instances of Excel.

      I hope that helps.

  • 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 ?

  • 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?

  • 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

  • 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?

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

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

  • 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?

    • 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

  • 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

  • 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!!

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

  • 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

  • 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!

    • 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!

  • 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

  • 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

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

      Thank you

  • 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?

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

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

    • 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!

      • 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!

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

  • 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!

    • 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

  • 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)

  • 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?

  • 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
      • 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! 🙂

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

  • 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
  • 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 🙁

    • 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

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

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

    • 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
      • 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! 🙂

    • 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!

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

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