Update Multiple Pivot Tables 20130618

Update Multiple Pivot Tables

In the screen shot below, one of the report filters in a pivot table is about to be changed. If you have multiple pivot tables in a workbook, you can use programming to update all (or some) of the pivot tables, if one pivot table’s filters are changed.

Pivot Table Report Filter

Here is one of the pivot tables, with a Report Filter being changed.

pivot table with Report Filter being changed
pivot table with Report Filter being changed

I’ve written sample code that updates multiple pivot tables, and one is changed, and last year, Jeff Weir shared his version of the code, which runs very quickly.

Pivot Table Code Benefits

Jeff’s code has another advantage too – in his version, 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.

NOTE: Jeff’s code is intended for pivots that DO NOT all share the same cache. If your pivots all share the same cache, then you can use my approach set out here: Filter Multiple Pivot Tables With Excel 2010 Slicers

To confirm whether or not your pivots share the same cache, you can use my code here: Excel Pivot Caches

Improved for Excel 2010 and Later

This week, Jeff is sharing his latest version of his code. It now checks the Excel version that you are using.

  • If you are using Excel 2010 and later, the code uses Slicers to change any pivot tables that share the same cache, as this is much faster. Any other pivots are then processed without slicers.

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.

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

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

Note: Jeff’s sample file was updated on June 16, 2013, so please download the new version if you have an older copy of the file.
__________________

0 thoughts on “Update Multiple Pivot Tables 20130618”

  1. When I try to use one of the filters on one of the pivot tables in my workbook, I get a “Subscript out of range error.” Do you know what may have caused this?

  2. I apologize, I realize it might help it I put the line of code I get an error at when debugging. Also, thanks for putting this together. I’m very new to writing macros and I am attempting to incorporate your code with an excel workbook at an internship that I am at. Any who, when I debug the code, the error is on this line:
    ReDim Preserve varExcludePivots(1 To i)

  3. Hi Steve. This is due to an oversight on my part. I take it you have commented out the strExclusions lines that tell the pivot to ignore particular fields or pages. I’ve got some code in there that writes any exclusions to a variant. Later on that same code checks the dimensions of that variant to see how many items are in it. I forgot to put some code in that handles the situation where there are NO items in it.
    Also, I very naughtily did not include any error handling, which is inexcusable really. Currently if the code bombs out, the user is left high and dry because I turned off the application ScreenUpdating and EnableEvents fields. I’ll whip up an error handler and ask Deb to post revised code shortly. (Sorry, Deb)

  4. Actually, I did have error handling, but had commented out the ‘On Error Goto Errhandler’ bit during testing, then forgot to enable it again. Doh!

  5. Hi Jeff. You are awesome. I’ve been playing around with the code all day trying to get it to work and I simply could not figure out how to do it. Again, I really appreciate the time and effort you’ve taken to put this together and share it with everyone here! I have one more question: The excel file I am working with has over 100 pivot tables. These tables pull from the same data source but are not at all related because they are drawing from different parts of a database. Will this have an overall negative effect on how the code will perform?
    Thanks again!

  6. @Jeff – I should also include the fact that the filters and fields are the same across all the pivots

  7. Good question…I haven’t tested this with pivottables that share an external data connection. I don’t know if slicers will work or not. We’ll soon find out.
    To fix the initial problem you mentioned:
    Uncomment the ‘On Error Goto Errhandler’ line (near the top)
    Add this:
    If strExclusions = “” Then strExclusions = “;”
    …in front of these lines (which are found in three places)
    varExcludeSheets = Split(strExclusions, “;”)
    strExclusions = “”
    Give the code a run, and tell me what happens.
    Also note that there is a series of comments between myself and IanB dated June 12 2013 at this thread that might be relevant to you:
    http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/
    The upshot is that if the pivots get automatically refreshed on workbook open, then such a refresh will then triggers the above code, which you probably do NOT want to happen. Rather, you probably ONLY want the code to run when a user manually triggers it.
    First, turn “Refresh data when opening the file” OFF for ALL pivots.
    Then, add this to the ThisWorkbook code module:
    Private Sub Workbook_Open()
    Dim wkb As Workbook
    Dim pc As PivotCache
    Application.EnableEvents = False
    Set wkb = ActiveWorkbook
    For Each pc In wkb.PivotCaches
    pc.Refresh
    Next pc
    Application.EnableEvents = True
    End Sub
    That should refresh all pivots when the file opens, but also NOT trigger my code.

    1. After following your instructions from above, I get an error. When I debug, the error states I have an invalid procedure or call arguement. The line is: bFiltered = Not pf_Master.AllItemsVisible from the modSlicers. I really appreciate your help.

    1. I apologize Jeff, I just saw this. Yes, my pivots are connected to OLAP cubes. Do you think there is any way to update all the filters of my pivot at once or is it not possible because my data source is cube?

      1. I think you might be out of luck. I tried my code with some pivots created by PowerPivot (which creates cubes) and it just doesn’t work. But that said, maybe that’s just because its PowerPivot, and maybe yours will work with my revised code. Let me know either way.

  8. Steve: This might be to do with the fact that your pivot gets data from an external database, or that the pivot is connected to a data cube. Can you share a sample file with me? [email protected]
    Also, I’ve got an older version of the code I could send you, that might work. It’s the code that this code replaced, and doesn’t use slicers. So it’s a bit slower. I can email you that, and we can give that a go.

    1. @Jeff – I am out of the office and won’t have access to the file. I’ll have to send you a sample first thing when I get back in the morning. Thanks again!

    2. Jeff – If I could get a copy of the code without slicers that would be great. I would like to give it a try.

      1. In the amended code, you can instruct it NOT to use slicers by changing this line near the top of the SyncPivotsAnyVersion routine :
        bUseSlicers = True
        …to this:
        bUseSlicers = False

      2. I actually forgot to mention that I did turn slicers off. I just overlooked the fact that I turned it off and saw slicers in the code which concerned me for a second. I did use your revised code, first with slicers on, and I received the same error. Then, turning slicers off, I also received the same error, which came from the modSlicers module and not the main code.

      3. Okay. Maybe the approach you will have to take is to NOT sync pivots, but instead use stored procedures at the database end to repopulate the pivots. i.e. someone changes a region to ‘West’, then you use VBA to trigger a SQL Stored Procedure from Excel with Dynamic Parameters so that they are all in sync. THe down side is the delay while all pivottables are refreshed.
        This might help:
        http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

  9. @Jeff – I will play around with this today and let you know what my findings are. Thanks again for all the help you’ve provided me with thus far.

  10. @Jeff – Hi Jeff. I finally was able to find a solution to my problem. It was surprisingly simple. I simply set up a slicer and then used the “pivot table connections” option with the slicer to connect it to all my other pivot tables and it worked out fantastically! No macro needed!

  11. Hi Steve. Could you record a macro while you do that, and then email me the code? I’d like to see how Excel does this stuff for pivots connected to OLAP sources (and potentially amend my code to suit) , but don’t have such a source.
    Glad it’s working. Obviously all your pivots share the same cache. My code is intended for pivots that don’t all share the same cache. It uses slicers for those that do, and another method for those that don’t.

    1. Jeff, just to clarify, you are asking if I could send you a macro of me setting up the slicer to link with all the tables in my spreadsheet, correct?

  12. If, possible, yes. ALso would be good if you would actually use the slicer to select something while the macro recorder is still running. I just need the code, not any actual workbook.

  13. Hi Jeff, many thanks for taking the time to do this. This exactly the sort of thing I’m looking for.
    Now, maybe I’m being a bit thick, but when I look at the sample file referred to above (PT0029 – Change Pivot Table Fields on Specific Sheets)I can’t get it to work. For example, on the ‘Sales Pivot’ worksheet, I change the region from ‘Quebec’ to ‘Ontario’. On the ‘Other Pivots’ worksheet, the selected Region in PivotTable1 remains as ‘Quebec’. Shouldn’t this change? (I have enabled macros and am using Excel2007). Grateful for any pointers… (another) Steve

    1. Yep afraid so, also tried a couple of weeks ago but had the same problem. I’ve applied the earlier version of this macro (i.e. Debra Dalgleish’s version that synchs all pivot tables)and it worked OK. Anyway, thought I’d flag it up in case other people had the same problem.

  14. Hi Jeff!
    Thanks for this great code!
    I have not tested all of the functions but what I have seen works correctly.
    Only one thing – one internationalization issue I realized.
    In my system the decimal separator is “,” (comma) so this row:
    If Application.Version >= 14 And bUseSlicers Then
    raises type mismatch error, because the version 14.0 is string and could not be converted to number. Our quick solution was:
    If Application.Version >= "14" And bUseSlicers Then
    Seems this way it works correcty.
    Cheers,
    Kris
    and The FrankensTeam

    1. Thanks Kris. One thing on my list of things to do on rainy days is bulletproof this code further. Among other things, it will error out if you already have slicers in the sheet, and I can’t have that!
      I’ll add your suggested tweak, and post a revised version back here in due course.

  15. Hi Guys,
    I was wondering if someone could assist
    i am tryin to refresh my data in a certain sequence using some vba code but keep getting an error, it used to work perfectly before but know keep getting error code 1004.(i use excel 2010)
    the code i am using:
    Sub RefreshAll()
    Dim PC As PivotCache
    For Each PC In ActiveWorkbook.PivotCaches
    PC.Refresh
    Next PC
    Sheet9.Range(“b1”).ListObject.QueryTable.Refresh BackgroundQuery:=False
    For Each PC In ActiveWorkbook.PivotCaches
    PC.Refresh
    Next PC
    End Sub

  16. Sabir – Probably the error is something to due with the Sheet9.Range(“b1”).ListObject.QueryTable.Refresh bit. Are you sure there is a querytable at b1?
    Also, why exactly do you refresh all pivotcaches twice? Only reason I can think of is that you might have a pivottable that uses anothe pivottable as a data source. Is that the case? If not, ditch that Sheet9.Range(“b1”).ListObject.QueryTable.Refresh bit and everything that comes after it.

  17. Hi, this looks great and is working perfectly. I am looking for a little alterations and am hopeless with code, any chance you can help out?
    I have a single data source and many workbooks and pivot tables working from this source.
    I would like only 1 of the Filters to change throughout the workbooks, leaving all other filters alone.
    Is this something this code can do? Or be altered to do?
    I am using Excel 2010.
    Thanks!!

  18. Jeff,
    I keep getting an error with this line:
    Set slrField = sc.Slicers.Add(ActiveSheet)
    Any help would be appreciated.
    Thank you.

  19. Hi Leigh. Sorry for the delayed response. Can you confirm the following:
    1. What version of Excel are you using?
    2. Do the PivotTables have different data sources? (Bonus question: do you know what cache each of them belongs to)

  20. Jeff,
    1. Excel 2010
    2. There are 4 pivottables actually, with 2 using the same one (PivotCache 2) and then 2 using the same one (PivotCache 3).
    Thanks,
    Leigh

  21. Ok. Do you have some slicers in the workbook that are connected to these pivots? My original code doesn’t account for that. I have a revised routine that I haven’t quite finished that takes a different approach: it syncs one PivotTable in cache A to another PivotTable in cache B, and it relies on Slicers to sync all other Pivots in Cache A with each other, and all other Pivots in Cache B with each other. That routine is in a book I’m working on, and the book should be available soon. I might ask Deb if I can post the code on her site too

  22. Jeff,
    Yes I do have slicers in the workbook connected. I thought your code uses slicers when possible. Am I misunderstanding? Sorry for the confusion.
    Thanks, Leigh

    1. Yes, my code does use slicers. It just doesn’t take into account the fact that you might already have some set up. Hence the error. My new approach – which isn’t quite ready for public consumption – is much more efficient. Flick me an email at [email protected] and I’ll send you a sample workbook of what I’ve got so far in regards to it. (I still plan to make it even better over the next couple of weeks.)

      1. Jeff,
        I have multiple pivot tables all connecting to different data sources and I’m looking for a way to sync them with a filter or slicer on the dashboard. They all have a common category “Portfolio” which would be what the filter is based on. I have pretty limited VBA knowledge, but have been searching through the comments on here and related posts trying to find an answer. It seems like you had something that might work going on last year – do you mind posting it?
        Thanks so much!
        Christy

      2. Hi Christy. I’m in the process of turning this into a commercial addin, so I can’t publicly share the code, I’m afraid. But if you’re interested in buying the commercial add-in, flick me an email at the above address.

Leave a Reply to Steve Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.