Update Specific Pivot Tables Automatically

In Excel 2010, you can use Slicers to change multiple pivot tables. However, you might be working in an earlier version of Excel, or you don't have room for Slicers on your worksheets.

Instead of Slicers, you can use programming to update multiple pivot tables automatically. In previous posts, I've shown how you can select items in one pivot table's Report Filter fields, and the Report Filter fields for pivot tables on the other worksheets will change to the same selections.

Specific Sheet and Pivot Tables

Jeff Weir has written an updated version of the code, which runs much faster than the previous version. You'll notice the speed difference especially if you're working with larger pivot tables.

Also, in this version of the code, you can specify:

  • any sheets you DON'T want the macro to check
  • any specific pivot tables that you DON'T want the macro to synchronize.

For example, only update the pivot tables on Sheet1 and Sheet2, and ignore PivotTable2 on Sheet1.

[Update: Sept 20, 2012] Jeff has made the following changes to the code:

  • you can now exclude particular PivotFields, plus if you change a pagefield in any pivot, the code will not only update pagefields to the same settings in other pivots but also change rowfields too.
  • added basic error handling so that ScreenUpdating and EnableEvents are restored to TRUE if anything goes wrong.

Jeff is also working on a version of the code for Excel 2010, that promises to be even faster -- so stay tuned for that!

[Update: June 16, 2013] Jeff has revised the code, so it uses Slicers if the version is Excel 2010 or later.

Making the Code Run Faster

In the previous version of the code, it looped through each master pivot field multiple times, to determine if each pivot item is visible or hidden. The corresponding pivot item in each slave pivot table was then set to the same setting. The code worked, but it was very slow in larger pivot tables.

The main reason that Jeff's code is faster is that it iterates through each master pivot field just once, so it can record only the visible items into a dictionary.

Then, for each pivot field in each slave pivot table:

  • All the pivot items are made visible
  • Items that are not in the dictionary's list are hidden.

Also, speed in Jeff's code is increased because it:

  • checks to see if.AllItemsVisible = true. If it is, no need to iterate through either the master or the slave pivot...it just makes all pivot items in the corresponding slave pivot fields visible. The old code looped through each pivot item
  • doesn't add items to the dictionary for checking if it has already found all the visible pivot items in the master list.

Modify the Code

If you download the sample file (see instructions below), you can copy the code to your own workbooks.

  • To see the code in the sample file, go to the Sales Pivot worksheet, right-click the sheet tab, and click View Code.
  • Then, to see the full code, right-click on the procedure name - SyncPivotFields - and click Definition

Here is where you'll change the sheet names in the SyncPivotFields code:

pivotupdatespecific01

Here is the section where you'll change the pivot table names:

pivotupdatespecific02

Download the Sample File

To download this version of the sample file, with Jeff's code, please visit the Sample Files page on the Contextures website.

Note: Jeff's sample file was updated on Sept. 20, 2012, so please download the new version if you have an older copy of the file.

In the Pivot Tables section, look for: PT0029 - Change Pivot Table Fields on Specific Sheets

The file is in Excel 2007/2010 format, zipped, and contains macros. Enable the macros when opening the file, if you want to test the code.

__________________

40 comments to Update Specific Pivot Tables Automatically

  • I've neve used dictionaries. Are they faster than simple VBA arrays?

  • Jeff Weir

    Jon...note that the reason my code is faster than Debra's previous code is because it iterates through each master pivot field just once, and records which fields are visible somewhere where that list can be reused again and again to sync other pivot fields. I happen to record that list in a dictionary, because a dictionary is perfect for checking if something is already in it. (You can either try to add the thing to it, and catch the error if that thing already exists, or (faster) you can test if the thing exists with IF dic.exists("Item to check) ...

    So I don't use a dictionary because of speed, and could have used an array (although I think that programming for the array approach would be more complicated).

    Forgot to mention two very good links on dictionaries vs collections:
    http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
    http://excelicious.wordpress.com/2010/01/07/dictionary-vs-collection/

  • Arrays need a little extra programming, but it's not really complicated, and I don't think it takes very long. You have to loop through the array until you find (or don't find) the item you're looking for.

    I should try these dictionaries.

  • Jeff Weir

    I do have an array approach in mind that I’m coding up. I’ll see if it makes any speed difference.

    Here’s another use for Dictionaries: as a substitute to a jagged array (i.e. a hierarchical data structure with uneven substructers). And here’s an example of a potential use of such a beast… implementing a robust “Custom View” functionality.

    As you know, the “Custom View” functionality built into Excel allows you to (among other things) save all your filter settings for a filtered list, and restore them later at will…PROVIDED you don’t have Excel Table (2007 or later version) in your workbook. That’s a major bummer, because Custom View was handy, but now is too risky to implement.

    Using a hierarchy of Dictionaries you could create a similar Custom View function that hold all the different settings of a Table – or even a PivotTable - so that you could recall those settings in future purely by iterating through that dictionary and restoring them.

    You could also use a variant holding a bunch of arrays to do the same, which might be simpler to implement. But I hypothesize you won’t pull as many chicks/dudes if using a variant compared with a custom dictionary.

  • Jeff Weir

    Darren asked me a question regarding this elsewhere that bears repeating.

    His question:
    In my case ALL of my PT's are on the same worksheet and All have different data sources how should I adjust the code and set a new Mater table.

    My answer:
    You don't need to set a 'Master' table...what happens is whenever you change any pivot page field on any pivot, that particular pivot becomes the Master, and all the other pivots (apart from any you specifically told the code to ignore) become 'Slaves'. The code doesn't care whether the pivots have different data sources or not.

  • Victor

    Thank you so much for sharing the good stuff with us. I have a question, is it possible to add an option in the code to specify the fields I need to automatically change (I’m asking because I don’t need all fields of each pivot table to be changed).

    Thanks in advance.

  • Darren Chapman

    Hiya Jeff, thanks for your response. This puppy has me very excited, I produce a lot of managemnt reports using PT's and I can see this code saving my ooodles of time. I am a regular on EF dot Com and I feel some others would appreciate this code. Would you mind if I link this page so others may benefit?

    If you have the time/inclination I have a couple of questions.

    Should I not wish to exculde ANY PT's do I just delete these four lines...

    Case "Other Pivots_PivotTable3"
    'do nothing
    Case "Other Pivots_PivotTable4"
    'do nothing

    Also, may I ask if this code can be ammended to update the PT's NOT from a master table BUT from a linked cell of a combo box?. If this question is beyond the relms of this Blog I totaly understand.

    Awesome work dude.

  • Jeff Weir

    @Victor….great suggestion.
    After this line:
    If pf_Master.Orientation = xlPageField Then

    ...add this

    Select Case pf_Master.Name
    '########################################################################################
    'Here's where we list any PivotFields in the master pivot that we want to ignore
    ' You simply list the pivotfield name in quotes, with the word Case in front.
    ' e.g. Case "SomePivotfieldWeDontWantToChange"

    ' You can add an optional 'do nothing comment on the following line if you like.
    ' This makes it clear to anyone reading the code that the macro does not proces this sheet

    Case "Item"
    'do nothing

    '########################################################################################
    Case Else

    And after this line (near the bottom of the code):
    Next wks
    …add this:

    End Select 'Select Case pf_Master.Name

    Then change the word Item in the Case "Item" bit to the name of the field you want the code to ignore. Add more Case "SomePivotfield" lines for any other pivotfields that you want the code to ignore.

  • Jeff Weir

    @Darren:

    Yes, just delete those lines:
    Case "Other Pivots_PivotTable3"
    'do nothing
    Case "Other Pivots_PivotTable4"
    'do nothing

    …although note that in your own workbook they won’t actually have any effect unless you happen to have a sheet called “OtherPivots” and happen to have pivottabes on those sheets called PivotTable3 and PivotTable4. But they should be tidied up otherwise someone looking at the code in future might be scratching their head.

    I’ve got some better code for linking pivots to a range or combobox that needs a bit more tweaking, but will provide to Debra to put on her blog in the near future. But another way you could use my code to do the exact same thing might actually be easier than using combo boxes.

    Say you’ve got a datasource with a column that has a list of fruit in it. E.g.
    Fruit:
    Apples
    Oranges
    Bananas
    Pears
    Peaches

    …and obviously there are other columns in your data to do with sales, revenue etc.

    If you wanted code to filter ALL pivots based on that list of fruit from a combobox, you can essentially do the same thing with the code as it is. Here’s howe: make up a tiny pivot that just points to the Fruit column of your data source (ignoring all the other columns with sales, revenue etc) and then put the Fruit field in the PageField part of your pivot layout (aka Report Filter in Excel 2007+) and then just hide the rest of the pivot. Users see just a dropdown box containing all the types of fruit in your data, but it’s actually a pivot that will sync all the other pivots.

    If you don’t follow, let me know and I’ll post an example file.

  • Darren Chapman

    Jeff, thanks for your response and the suggestion. I have implemented this method (fruit) for the time being and i look forward to seeing your tweaked version in the future. Combo box' are good for this job when you have a long list as the auto complete function saves scrolling down half a mile!

    I'll be using this code A LOT in future, including the adapted version above (Victors post).

    Thank you very much for all your time. You have made my week

  • Jeff Weir

    @Darren...out of curiosity, are you selecting just one entry from the combo box or several?

  • Darren Chapman

    Morning. just one entry mate

  • Jeff Weir

    In that case, provided you're using Excel 2010 (and 2007 too from memory) then putting your text in the SEARCH box that comes up when you click on the pivot field filter icon will be every bit as good as autocomplete.

  • Darren Chapman

    Hiya Jeff. Good idea - but that search facility is only available in 2010. Shame! good excuse for the company to upgrade. My mate is in NZ at the moment and just sent me a video of him doing the Nevis Swing and Bungee jump. Looked scary!

  • Jeff Weir

    What's his name, I'll keep a look-out for him ;-)

  • Jeff's sample file was updated on Sept. 20, 2012, so please download the new version if you have an older copy of the file.

    http://www.contextures.com/excelfiles.html#PT0029

  • Jeff Weir

    Thanks Debra. The revised code means you can now exclude particular PivotFields, plus if you change a pagefield in any pivot, the code will not only update pagefields to the same settings in other pivots but also change rowfields too. And lastly I've added basic error handling so that ScreenUpdating and EnableEvents are restored to TRUE if anything goes wrong.

    I'm still working on an approach that uses slicers (2010 only) that will be faster than this, as well as some non-slicer approaches that will also be faster.

  • Darren Chapman

    Hiya Jeff, Thanks for the revised version which I've just taken for a test drive. Wow, it's fast, its flexible and the 'descriptions or operating procedures are clearly written. many thanks, this code will allow me to do my job better. If I get a pay rise i'll buy you a pint!

  • Jeff Weir

    Darren...get your mate to buy me a pint when he's in Wellington. In fact, with the power of your currency to mine, he can buy me several. THen we'll skype you to show you the results!

  • Luis Rodrigue

    Hello,

    I used the updated code which is a wonderful thing by the way! It was exactly what I was looking for. The only thing though is when I click on Select Multiple Items checkbox for the date report filter and try selecting any dates all the date fields in the other pt's have "blank" selected. It works great if I leave the Select Mulitple Items unchecked and choose only one item. It would be nice to have the option though to select multiple items. Any ideas?

  • Luis Rodrigue

    Re-pasted code and refresh problem solved...

  • Eddy

    Great Macros, This will be very helpful for my project. Thanks for the upload.

    Please forgive me but I need to ask how do I copy the Macros to another file? (I'm just beginning with Macros and I only know how to record one).
    I

  • Eddy

    @Debra Dalgleish

    Thanks for the heads up, but it seems my previous post was not reflected complete. I was doing the following to copy the macros.
    1.-Right click on the sheet
    2.-View code
    3.-Insert, click on Module
    4.-Copy and paste the Macros to my file.

    Anyway I have tried the method you suggested me, but it won’t work too. I’m using excel 2007 and the file I downloaded (Jeff’s file) works perfect but I can’t make it work on any other file. I do have the enable all macros option “checked” in the security options. I’m also using a Macros enable file in case you were wondering.

    Any thoughts????? Am I missing something??????
    Thanks in advance your support is greatly appreciated.

  • Jeff Weir

    Eddy...can you share your workbook with me? Just dump any confidential data, or replace confidential numbers with rubbish ones. Email is weir dot jeff at gmail dot com

    Otherwise, what kind of errors are you getting? Or are you getting no error at all..i.e. its just not doing anything?

  • Eddy

    Hi Jeff,

    Thanks for your support. I'm not getting any error is just like you said not doing anything. I had tried creating a new file from scratch, but I always get the same result which is "nothing". I do know it might be my lack of knowledge (puppy knowledge) and I appreciate the people like you guys who help us to improve our skills.

    Anyway tonight when I'm at home I will send you my book (where do I send it?).You can send me your email here:

    applereloaded77@hotmail.com

    thank you guys for everything.

  • Jeff Weir

    Hi Eddy. Note that to get the code to work, putting the code in a code module is only half the battle. You also need to put an event handler routine in each sheet module that you want the code to work on.

    That is, if you have a sheet called "My cool report" and another called "Some other report" where there are some pivots that you want to be synced, then look in the right pane of the VBA editor and you should see something like this:
    VBAProject (WORKBOOK NAME)
    Microsoft Excel Objects
    Sheet1 (My cool report)
    Sheet2 (Some other report)

    Double click on the line that correstponds to each sheet where you want the pivots to be synced, and insert the following event handler:

    Private Sub Worksheet_PivotTableUpdate(ByVal target As PivotTable)
    SyncPivotFields target
    End Sub

    What this does is it keeps a lookout for any pivots in the particular sheet, and if one is refreshed it stores that name in a variable called "target" and then says "hey, SyncPivotFields code...do your thing on the pivottable who's name is in stored in this "targed" variable.

  • Eddy

    Genius!!!!

    Thank you so very much.

    I was missing that part. Now it works perfect.

    Thanks again for everything

  • Darren Chapman

    Hi Jeff, just to feedback. I demo'd the dashboard to my line mgr yesterday (the d'board which employed your code) and she loved it. It could have been done using formulea (in fact it was done this way until I stumbled across your code) ect but it would not have been as fast or robust. So, many thanks...... Also, I really like the way you explain to users how it works in PLAIN ENGLISH. Nice one dude

  • Darren Chapman

    Hi Jeff - something has come up during testing. But before I go into detail here I thought that i'd email it over to you first, just in cast it "Expected" Cheers

  • Julio

    Hi,

    I just start to use this code for a Excel file with multiple Pivot Tables in multiples worksheets. The first time I received an error within the following line: bFiltered = Not pf_Master.AllItemsVisible (Invalid procedure call or argument), the second time, I change the line to: bFiltered = Not pf_Master.CubeField.AllItemsVisible and the process continue til other point and stop again. I'm not sure why the errors, could be probably because my data source is a cube? How will be the version for OLAP Cube Pivot Tables?

    Thanks.

  • Rick Freitas

    Hi Debra,

    I have the this code working, and is amazing! What I would like to do is have a drop down data validation field on my dashboard where user can select item in filter (in my case country) that will then update all the pivot table filters for country to be the same in all the PT's that have this code running. Can you please help with the VBA code to make that happen?

    Thanks,

    Rick

  • Rick - an easy way to do this is replace that data validation dropdonn with a pivot that only has a country page field and nothing else. When the user selects a country or countries from this page field then all the other pivots will be updated accordingly.You may need to hide some rows/columns so that only the pivot page field can be accessed by the user.

  • Rick...also theres a comment above from sept 17 2012 where I also discuss this approach.

  • Rick Freitas

    Thanks Jeff, after I sent email above had read thru all the comments and found what you mentioned, and works like a charm! Great code and can't believe how fast it is, and really appreciate being able to use it. Only one thing that I will mention in case others might have same issue, was having problem with one PT not updating. I am using a page field called Area, all the PT's are connected to SQL tables, and the Area field is spelled with uppercase "A" (ie.. "Area") in all the ones that were working, where the one PT that wasn't was spelled with lowercase "a" (ie.."area"). That was the only difference I could see, so sheepishly asked my SQL dev to change it to uppercase "A" for Area, after hours of scratching my head on why it wasn't working, and it worked! Not sure if that is something that can be rectified in code, but either way, thought I would mention in case anyone else runs into same problem. Thanks again!

    Rick

  • Jeff Weir

    Hi Rick. Glad you like it. Yep, this code is pretty fast, although if you have pivots with tens of thousands of items in them, it will take a while. THat said, I'm working on two approaches that will sync very large pivots nearly instantly. One uses slicers to do this (which requires Excel 2010 or later), and the other uses some very very sneaky tricks that I'm not ready to share yet, because I want to turn it into a commercial add-in.

    In regards to your issue, this code can be made case insensitive very very easily. Just add this line:
    dicPivotItems.CompareMode = vbTextCompare

    ...after this line:
    Set dicPivotItems = CreateObject("Scripting.Dictionary")

    ...and that should do it. Haven't tested it, but basically the Dictionary object that I use to check whether or not an item in one pivot exists in the other can be either case sensitive (which is the default setting) or case insensitive. So that one line should do it.

    I'd be pleased if you can try it on your old data and let me know if it fixes the issue.

  • @Rick - try the new code at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ that as discussed above uses slicers for any pivots that share the same cache, and the old code for any that don't (or if the user doesn't have Excel 2010)
    @All - be sure to update your code too...you should notice a substantial speed improvement.

  • Al Delgado

    A somewhat related question, I downloaded file PT0031 - Change All Pivot Charts With One Filter from you site. On the Display tab there is a Report-field-only pivot, filter. The Instructions tab says to "Create the Report-field-only pivot (pvt.Select) as a selector (from the same Pivot Cache)."

    How do you create the Report-field-only pivot? I want to place the filter on a different tab, I know I can do this with a slicer, but I want the look of the filter drop down.
    Thank you

  • Kajaj

    I used the code and it works great...awesome job, but i have a question, as much as i want to update all the pivot tables based on the one filter in this case the Region, i will like for the user to be able to select other filters such as Date, Item or Employee on each worksheet without updating every other pivot table. in other words, i only want the filter Region to update all pivot tables in in the workbook when selected, but if another filter is selected in any pivot table, it should be specific to that pivot table....

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>