Update Multiple Pivot Tables 20130618

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.

updatemultiplepivottables01

I’ve written sample code that does this, and last year, Jeff Weir shared his version of the code, which runs very quickly.

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.

__________________

36 comments to Update Multiple Pivot Tables 20130618

  • Steve

    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?

  • Steve

    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)

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

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

  • Steve

    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!

  • By database do you mean external database e.g. Access or SQL Server etc? Or do you mean some data stored in an Excel spreadsheet?

  • Steve

    @Jeff - It is an external database we are pulling all the data from.

  • Steve

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

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

    • Steve

      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.

    • Steve

      Also, the file does not automatically refresh when opened.

  • Also, I don't think this code will work for any pivots that are connected to data cubes.

    • Steve

      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?

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

  • 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? weir.jeff@gmail.com

    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.

    • Steve

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

    • Steve

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

      • 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

        • Steve

          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.

  • @All: I've made some amendments to the file following Steve's comments above. You can try the amended file out by downloading it from the following link:
    https://www.dropbox.com/s/firfku1vr7dy4o3/Sync%20Pivottables%20using%20slicers%2020130621%20v2.xlsm

    Once I know it's stable, I'll get Debra to replace the existing file in her link above.

    Please post back if you have any problems with the file.

  • Steve

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

  • Steve

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

  • Jeff Weir

    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.

    • Steve

      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?

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

  • And you don't need to link it to all tables...just a couple will do. Thanks Steve.

  • SteveRD

    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

  • Might be a version thing...works fine on my Excel 2010 version. What happens if you restart Excel? Same deal?

    • SteveRD

      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.

  • 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

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

  • Sabir

    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

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

  • erinmeg

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

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>