Change All Pivot Tables With One Selection

Change All Pivot Tables With One Selection

There is a new sample file on the Contextures website, with a macro to change all pivot tables with one selection, when you change a report filter in one pivot table.

Change All Pivot Tables

In the sample workbook, if you change the “Item” Report Filter in one pivot table, all the other pivot tables with an “Item” filter will change.

They get the same report filter settings that were in the pivot table that you changed.

Change All Pivot Tables With One Selection

Select Multiple Items

In this version of the sample file, the “Select Multiple Items” setting is also changed, to match the setting that is in the pivot table that you changed.

In the screen shot below, the Item field has the “Select Multiple Items” setting turned off. If any other pivot tables in the workbook have an “Items” filter, the “Select Multiple Items” setting for those fields will also change.

pivotmultichange02

How It Works

The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.

For each report filter field, the code checks for the Select Multiple Items setting, to change all Pivot Tables with the same report filter field.

The code loops through all the worksheets in the workbook, and loops through each pivot table on each sheet.

Private Sub Worksheet_PivotTableUpdate _
  (ByVal Target As PivotTable)
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
  bMI = pfMain.EnableMultiplePageItems
  For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
      If ws.Name & "_" & pt <> _
          wsMain.Name & "_" & ptMain Then
        pt.ManualUpdate = True
        Set pf = pt.PivotFields(pfMain.Name)
          bMI = pfMain.EnableMultiplePageItems
          With pf
            .ClearAllFilters
            Select Case bMI
              Case False
                .CurrentPage _
                  = pfMain.CurrentPage.Value
              Case True
                .CurrentPage = "(All)"
                For Each pi In pfMain.PivotItems
                  .PivotItems(pi.Name).Visible _
                    = pi.Visible
                Next pi
                .EnableMultiplePageItems = bMI
            End Select
          End With
          bMI = False
        Set pf = Nothing
        pt.ManualUpdate = False
      End If
    Next pt
  Next ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Download the Sample File

To test the Change All Pivot Tables code, you can download the sample file from the Contextures website.

On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 – Change All Page Fields with Multiple Selection Settings.

The file will work in Excel 2007 or later, if you enable macros.

Watch the Video

To see the steps for copying the code into your worksheet, and an explanation of how the code works, watch this short video.

______________________

272 thoughts on “Change All Pivot Tables With One Selection”

  1. Happy New Year! Forgive me for asking, but I’m a VBA rookie. Is there a way I can move this code over to a macro so I can use a button to run the update? I have a fairly complex pivot table setup that takes a minute or so to update every time I change a page filter. My two page filters are related so don’t want all of the pivot tables to update until the user changes both of them.
    Thank you!

    1. @Sachin, Happy New Year! Yes, you could run this from a button, instead of event code. You would create a macro, with similar code, and assign that macro to your button.
      In the code, instead of Target, you would define the specific pivot table that the other pivot tables should copy.

      1. Hi Debra,
        Dim ptMain As PivotTables
        I see ptMain is defined as (PivotTables). So, can I assign multiple Pivot tables to the variable ptMain? If so, how will I do it?

  2. Debra, I’ve adapted your previous version of this code for a couple of projects and it’s been very helpful. I’ve never needed the “Select Multiple Items” functionality, but when I do, I’ll know where to look!

  3. Debra,
    This is a great code! How can I apply it to just a few Pivot tables and not everything in the file.
    Thank you,
    Iassen

    1. Hi Iassen- I was just wondering if you ever figured this out since you asked this question a few months ago?
      I am trying to find a way to chg pivots in one worksheet vs all worksheets. Please let me know.
      Thanks in advance,
      Becca

  4. Hi Debra
    I think this is great code too! I am also wondering as per the above comment whether it’s possible to update just a few pivot tables and not everything in the worksheet?

  5. Hi Debra
    This is brilliant!
    I want to alter it slightly to a) just work on 1 worksheet and b) to just change one pivot field (I have 2).
    I thought I’d be able to just remove the “For Each ws In…/Next ws” lines but when I do this it doesn’t work at all. Is there something else I need to change?
    To get around the issue of one field remaining constant, do I need to specify the field that can be changed?
    Many thanks
    Jenny

    1. Hi Jenny,
      To use the code on one sheet for a specified field, change the start of the code to the following, and remove the end lines for Next ws and next pf

      Application.EnableEvents = False
      Application.ScreenUpdating = False
          Set pfMain = ptMain.PivotFields("Region")
          bMI = pfMain.EnableMultiplePageItems
              For Each pt In wsMain.PivotTables
                  If pt <> ptMain Then
                      pt.ManualUpdate = True
                      Set pf = pt.PivotFields("Region")
                              bMI = pfMain.EnableMultiplePageItems 
      1. for some reason my code is not working. I am just trying to change one specific field on a worksheet for all pivot tables.
        Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
        Dim wsMain As Worksheet
        Dim ws As Worksheet
        Dim ptMain As PivotTable
        Dim pt As PivotTable
        Dim pfMain As PivotField
        Dim pf As PivotField
        Dim pi As PivotItem
        Dim bMI As Boolean
        On Error Resume Next
        Set wsMain = ActiveSheet
        Set ptMain = Target
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Set pfMain = ptMain.PivotFields(“open date”)
        bMI = pfMain.EnableMultiplePageItems
        For Each pt In wsMain.PivotTables
        If pt ptMain Then
        pt.ManualUpdate = True
        Set pf = pt.PivotFields(“open”)
        bMI = pfMain.EnableMultiplePageItems
        With pf
        .ClearAllFilters
        Select Case bMI
        Case False
        .CurrentPage = pfMain.CurrentPage.Value
        Case True
        .CurrentPage = “(All)”
        For Each pi In pfMain.PivotItems
        .PivotItems(pi.Name).Visible = pi.Visible
        Next pi
        .EnableMultiplePageItems = bMI
        End Select
        End With
        bMI = False
        Set pf = Nothing
        pt.ManualUpdate = False
        End If
        Next pt
        Next ws
        Next pfMain
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        End Sub

  6. Hi Debra (and all others who had the same question as I do :)),
    How can I change the code to have it work on a single worksheet with two pivot tables?
    Thanks in advance,
    Becca

    1. Hi Becca, you can get rid of the “For Each ws..” and “Next ws” lines, then change the first few lines to the following:

      For Each pfMain In ptMain.PageFields
          bMI = pfMain.EnableMultiplePageItems
              For Each pt In wsMain.PivotTables
                  If pt  <>  ptMain Then
                      pt.ManualUpdate = True
      1. Debra! I wanted to write a quick note to thank you very much for writing and providing these codes! Here I am, years after you posted this code, absolutely grateful you made this available! As someone who is only looking at VBA code for the first time today, your video was extremely helpful, too, especially how you explained what each line of code is performing! You do wonderful work!

  7. Yeeaay!! 🙂 It works,thank you so much!!
    I spent so much time looking for the right code to do this.
    I appreciate it very much,
    Becca

  8. Perfect!
    It seems so easy once the solution’s right in front of me but just couldn’t get there on my own!
    Thank you so much
    Jenny

  9. Hi Debra,
    I’ve found this code extremely useful, many thanks for posting it.
    I’ve run into some issues recently though when using in conjunction with pivot charts
    When I run the code, I lose the formatting on the charts that are being updated from pfMain, i.e. they revert back to the default format.
    If I step into the code, it works fine and all formatting is preserved. Any ideas why it might only be occurring when left to its own devices?
    Thanks
    Tim

    1. Hi Debra, just letting you know that I’ve also posted the question on Mr Excel. Suspect it is not code related per se – perhaps a more general Excel issue that someone may be able to shed some light on.
      Cheers
      Tim

    2. Resurrecting an old thread here, but I had the same issue and found a solution that works for me (Excel 2007).
      Like you I lost chart formatting when running the macro, but it worked fine if I stepped through it!
      Removing the pt.ManualUpdate = True/False lines solved the problem for me, and didn’t appear to cause any issues. Hope this is helpful to someone.

  10. Hi, this would work great for me if you could help me change one thing. I have 4 pivot tables all with 2 report filters, on separate worksheets. I just want this code to work on one of the report filters titled “Open Date”
    If you could help me out, it would be wonderful. I tried changing the original with some of the code you gave to becca but I could not get it to work!

  11. hi,
    i want a code in excel 2007 which will change my pivots fields as i change in my drop down.
    please suggest me any code or formula, so that i can apply to my list or combo box. In short, whichever field i will choose from my combo box or from list box, same selection should reflect to over my pivots field..
    thanks

  12. Hi Debra,
    Thank you for this. How can I work it so it updates dates? It works fine for text etc but when I want it to update for dates it won’t do it! I’ve even tried converting the dates to text and still no joy! Any help would be great.
    Kind regards,

    1. Yes I Had the same problem. I had standard short date format, and it worked if I selected one date (multiple items disabled) however once I enabled ‘select multiple items’ and selected two dates, it just made all my other pivot tables set to All. Any help would be much appreciated!

  13. Hi Debra,
    I find you blog really very useful but I have still not able to find the solution to my problem. Problem has been narrated by few other users at other places but still solution is missing.
    The problem is as follows: (excel 2007)
    On double click the pivot table creates a new sheet having the filtered data. I want that on a double click the source data should open sheet having source data and just show the filtered data. This is needed if someone want to do some editing and then see the effect in pivot table. this way can move back and forth and do the fine tuning easily.
    After searching the net for almost two days I feel you are the one who can possibly provide a solution. I will be very grateful if you provide some help. Further if you feel it is not possible I would like to hear even that also so that I do not waste any more time on this issue.
    Thanx and Regards

    1. SKS, were you able to prevent opening of new sheet on double click of pivot?
      i need that since the new sheet that opens, is losing the hyperlink on he data in one of the columns available in the source sheet….so unless someone can help fix the hyperlink on the new sheet, i need to open the source sheet on double click i guess!

  14. Debra,
    Great code! I was wondering how to alter it so that I can exlcude one or two worksheets? I still need to be apply a unifrom filter across three sheets, but I’d like to exclude two worksheets from the event. Is this possible? Thanks a ton.

  15. Hi Debra,
    Wow.. this the BEST working macro I have ever come across to handle multiple pivots in excel.
    The site has taken place in my favorites 🙂
    Thanks a lot
    Regards,
    Amit

  16. Hi,
    I tried to use the code but it didnt work for me. I am using OLAP cube as the pivot table’s source.
    Can you please show me how to revise the code when OLAP cube is the data source?
    Many thanks,
    May Lanie

  17. Hello Debra,
    First and foremost, very helpful code!
    My question is the following:
    The filtering of the tables works just great, all filters are do change as I am moving along the pivot tables, my focus is the following:
    Rather than repeat the same filter settings for the multiple pivot tables I have, I want to filter the values just once and compare data, however, If I do want to change one variable, say “Year”, then when I do filter for the year on any of the other Pivots, it automatically filters for the same year on the subsequent pivots, what would you recommend would be the best workaround for this issue?
    Regards,
    Andres

    1. @Andreas, you could set up a cell where you enter yes/no, and refer to that cell at the start of the code. If it’s yes, then run the filter code, and if it’s no, exit the macro.

      1. That’s exactly what I ended up doing! Great piece of code though, very useful.

  18. Hi Debra,
    How about one or two single fields but across all spreadsheets? Could you please post the code for it? Would it go in each spreadsheet or in “thisworkshop”?
    Thank you so much

      1. Hi Debra,
        Thank you for the great codes and lessons. They have saved me in several instances. You are really great.
        I am trying to filter multiple items in 2 pivot tables in different tabs using the macro in ,”PT0025 – Change All Page Fields with Multiple Selection Settings”. The filter names are same for both pivot tables which the data sources are different. There are common items amoung multiple items to select in the drop down as well as different items ( though the filter name is same).
        When I apply multiple filters to one pivot table , the other tables picks listed items and some other non-listed items from the list of items in the 2nd pivot table. At times the 2nd tables picks entirely differengt items, if the selected item in pivot 1 is not available in pivot 2.
        Could you please have a look at this issue.
        Many Thanks ,

  19. Debra, Great job on this. I have been looking for a solution for this for about six months. I have an issue that when I am refreshing the pivot tables it is taking a very long time, 31 minutes. I have 18 pivot tables on one worksheet, refreshing took less than a minute before I put this code in. I am using 2007. Do you have any ideas on this?

    1. @Kevin, you could check the sheet name, and exit the code if it isn’t the active sheet.
      Add this line after the Set wsMain line:
      Set wsMain = ActiveSheet
      If wsMain.Name <> Me.Name Then GoTo exitHandler

  20. Debra,thanks for this amazing code. However I seem to be having some problems with refreshing pivot. After I refresh my pivot, all my filters are reset and I have to re-filter them. Do you know what might be causing this? Thanks

  21. Can the pivot table create time interval? For example, I have a set of data, I would like Pivot Table group the time interval as I wish and plot the chart. Say every 15 minutes or 30 minutes or 1 hour. Can it do the job?

  22. Hi Debra. I have the same question as May Lanie posted on June 27. I am using OLAP cube as the pivot table’s source. Can you please show us how to revise the code when OLAP cube is the data source?
    Thank you very much for sharing this great code.

  23. Hi Debra,
    I agree, this is nice. I have a comment and a question.
    Comment (for Excel 2010, for sure): for those who don’t like double-clicking on a cell of a pivot table and having the data behind that value pop up in a new worksheet: This can be turned off. If you right-click the pivot table and select “PivotTable Options”, then go to the “Data” tab, there is an option you can uncheck titled “Enable show details”. When this box is unchecked, the new worksheet will not pop up with the underlying data.
    Question: I have been looking for code to alter the actual “report filters” in several pivot tables at once. Say that I have your workbook of data and I want to add “Date” to the report filters for all pivot tables at once. How would you do that? The way it stands now, I would have to go from one pivot table to the next and add “Date” to the report filter. So, Essentially, I want to be able to format all of my pivot tables’ report filters identically.
    thanks!

  24. If you’ve got excel 2010 you might know about slicers, which effectively let you do the same thing without all that looping, and without any VBA whatsoever.
    On the downside,
    1. slicers can take up quite a bit of screen real-estate (although if you have the Microsoft PowerPivot addin installed, they are much improved). But you can always add a slicer somewhere where users don’t see them, and add a smaller listbox or similar where users can select the same subset of things that appear in that oversize slicer.
    2. slicers only work with pivots that share the same pivot cache. But you can always add a hidden slicer for each pivot cache, and keep them synchronized where appropriate with VBA.
    Which brings us to the upside: slicers address your pivotfields directly. That is, you don’t have to iterate through each field in each pivot on each sheet – which can take a heck of a lot of time if you’ve got pivots with a couple of hundred thousand items in them.

    1. thanks! I had heard of slicers before but hadn’t explored them yet. It took me a little to figure out how to get it to work with all my pivot tables. But it works great, and without VBA.

  25. Trying desparately to get this coding to work, but mine keeps doing something funny. I have many pivot tables on the same worksheet and they are set to be filtered by the same data item. This data item selectiosn consists of the numbers 1 through 10, which along with the ‘all’ gives eleven choices for the filtering item. When I put in the code and do not use the ‘select multiple items’ option, I can handily update the filter items on all pivot tables just by adjusting one. However, when I set it to ‘select multiple items’ (which is what I want), the weirdest thing happens; all pivot tables set to ‘9’ except the the table on which I made the change. I have no idea why it chooses ‘9’ (it always does no matter what selection I make on the active pivot table). Any ideas why this is happening?

  26. I have a report which has multiple pivot tables and pivot charts. I have set up a separate pivot table at the top with a number of filters (Month, Group etc) which are to filter all pivots tables and charts in the report. The macro works great with fields when they are in the ‘Filter’ section of a pivot table or chart. However I also want it to filter the fields of the tables if they appear in the ‘Row Labels’ section, which only seems to work if the report filter (at top of page) has ‘select multiple items’ enabled. However more often than not I only require to select one at a time – therefore would you be able to make this work if the field appears in the row label, no matter whether the overall report filter has multiple items enabled or disabled?

  27. @Robert…I tried the code on a sample dataset that uses numbers, and it works fine for me. What version of Excel are you using? If 2010, using Slicers is an alternate (and simpler) approach. If using another version, can you post a sample file somewhere so I can take a look?

  28. Hi Jeff, I’m using Excel 2007. I’m running pivots off a massive data base that is the input from a consumer survey. There are questions that are 1 to 10 ratings. I have created dozens of pivot tables for which the desired filter is 8,9 and 10 out of ten(showing a high rating for the particular question). I cut and paste the code as provided here, and when I do multiple selections, it just sets everything to nine. I’m afraid I can’t show you what I’m doing because the data I am working with is confidential, so I suppose I am on my own….thanks anyway…

  29. Hello Debra,
    Truly great work, top notch! Thanks so much for sharing! I have one question and I almost feel bad asking it… I feel like I’m asking too much. I was wondering if there was a way I can get a selection in one page field to change another distinct page field. Example, changing page field “Month” to January will change page field “Quarter” to Q1. I’m not sure if this is just a pipe dream but in any case thank you for the code you’ve already provided and thanks for your time.

  30. Hello,
    I have a 6 pivot tables ( 6 tabs ) that are all built from the same data set. In fact, I built the first pivot table with the max number of columns, than just copied it to the new tabs and got rid of the columns I did not want in that particular tab ( so I can eventually build a chart in said tab ). Anyway, there are 4 filter fields. I followed the directions in your video as precisely as possible, but no matter what I do, changing one of the filter fields does not impact the other pivot tables. Do I need to “turn on” something in Excel, maybe? Or maybe the fact that I have 4 different filter fields is causing a problem? I copied your code precisely from the downloaded example SS. I am not a VB expert, so I might be missing something simple. Thanks.

  31. @Robert…I’ve had some issues in the past with pivot table code that inexplicably treats numbers as text. Maybe that’s what’s going on. I got around the problem in the end. If you can strip all but a few lines from your data source and make them commercially unsensitive, then feel free to email it to me at weir dot jeff at gmail dot com and I’ll take a look.
    @Tony…can you post your workbook somewhere with dummy non-sensitive info or email it to me at the above address…happy to take a look.

    1. @Jeff: How/where do you want to to submit it? It is just a SS to track my weight and related items, so nothing really sensitive. I guess I can change the numbers, or reduce the data set, rather easily. What is bugging me is that I followed the directions in the video precisely. Anyway, thanks for your help.

  32. Hi Tony, email it to me at weir dot jeff at gmail dot com and I’ll take a look and see if I can work out why it’s not working for you. Regards, Jeff

  33. @Tony. Got your workbook, and it works fine on my machine on Excel 2010.
    Some possible reason for why this runs on my 2010 version but not on your system:
    1. maybe it’s a compatability issue that is being hidden from you because of the “On Error Resume Next” statement at the top of each procedure. this statement tell Excel to ignore any errors, and just keep trundling through teh code. Can you comment out that ‘On Error Resume Next’ statement, (i.e. put an apostrophe ‘ in front of it) and run the code, and see what happens?
    2. Or maybe your machine doesn’t let you run macros? Can you check that your macro setting is NOT set to “Disable all macros without notification”? See http://www.dummies.com/how-to/content/how-to-set-the-macro-security-level-in-excel-2007.html
    @Debra…Is the “On Error Resume Next” statement tackling a particular scenario?

    1. Thanks Jeff. I started looking at option 2 and that is when I noticed the warning bar up top about macros being disabled. I guess I am not used to looking at Excel documents with macros, because I never noticed that before. Anyway, I just have to enable it whenever I go into this spreadsheet. This is fine – better to be paranoid and secure, then get hurt by some macro I am not even aware of. Thanks again.

  34. Hi great code works a treat. There is one issue I am encountering with speed. I have a macro which sets the filter defaults to make it easy for users. However I have plenty of pivot tables in the workbook so it takes a while to scroll through all of them. However there is only a couple of pivot tables that have the field I want to change, therefore is there a way I could tell the code to only look through the worksheets that have the pivot tables. So for example only run this code for worksheet, 1 2, 3 and 4 and exclude worksheets 5-16?

  35. @Ryan…if you’re using Excel 2010, then as per my comment above you can use slicer to address multiple selected pivottables directly , and because they address only the desired pivotfields in each pivottable directly, you don’t have to iterate through each field in each pivot on each sheet (which as you’ve found can take a heck of a lot of time if you’ve got pivots with a couple of hundred thousand items in them.)

    1. Thanks jeff. I do use excel 2010 on my local pc which as you say only affects the selected tables, however our clients only have excel 2007 so I can’t use slicers when deploying to a client server which is why I am interested to know whether it is possible to use this code but only for a select few worksheets

      1. Hi Ryan. It is, and I’ve tried to post code here a couple of times but for some reason my comment doesn’t show. I’ll try and post it here again, but if it doesn’t show, you can email me on weir dot jeff at gmail dot com and I can email you a file back.

      2. Hello all,
        This code and blog are great and very helpful.
        I have the exact same problem as both guys above. I too would really appreciate it if there could be a solution to this problem.
        Thanks for sharing your knowledge

  36. Hi Debra. One potential bottleneck in the above code you’ve posted is that it effectively iterates through each master pivotfield multiple times, in order to a) determine whether each pivotitem is visible or hidden, so that the corresponding pivot item in each slave pivottable can be set to the same setting. In my experience, iterating through pivot items is very very slow compared to iterating through an array, or adding things to a dictionary.
    Given this, for large numbers of pivottables and/or with large numbers of pivotitems it would be much faster to iterate through each master pivotfield just once so that we can record only those items that are visible into a ‘lookup’ array or dictionary.
    Then you can then iterate through every corresponding slave pivotfield (after making all items visible) and hide just those items that are not in that pre-populated master list. The difference being that we only have to do a ‘slow’ iteration through that master pivotfield once, and on subsequent ‘passes’ we can do a much faster iteration through an array or dictionary that contains a potentially much smaller subset of items (i.e. only those that are visible)
    I’ve got some code half built that does this. Furthermore it checks the slave pivotfield against the master list in a very economical manner that I think would make even Charles Williams whoop out loud.
    I’ll complete it as soon as I can, and post it here.

  37. Hi Debra, Excellent blog btw. Very helpful. May I ask if this would be possible if the pivot tables all had different data sources? Ie, I have a report wherby there are 6 PT’s on one sheet, each have there own source. However, I would the user to only have to choose the fiter value once. I tried using a combo box but to no avail. Do you have any pointers? Many thanks. Darren

  38. @Jeff Weir. Hi Jeff many thanks for your reply which i have just picked up @ 09:49 GMT. I’ll check out the link now and post back. Thanks one again. Looking forward to seeing a new faster approach! All the best

  39. @Jeff – holy smoking raisens… that is some piece of coding.
    May I ask,
    In my case ALL of my PT’s are on the same worksheet and All have different data sourcs how should I adjust the code and set a new Mater table. Sorry to trouble you. Many thanks Darren

  40. Hi Darren. Thanks for the feedback. If you like that, you’ll love the new approach when I post it!
    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’. Keep the questions coming…I’m keen to make this puppy as easy for people to use as possible.

  41. Hiya Jeff, thanks for your response. Much appreciated and for the keep the questions coming offer. This puppy has made me very excited as i’ve be trying for the last week to achieve something like this. There are snippets all over the internet (but nothing as comprehensive as this) and I have posted questions on EF dot Com. Would you mind if I link this page to my own question so others may benefit?
    May I ask, can this code be ammended to update the PT’s NOT from a master table BUT from a DV list/combo box linked cell ect. If this question is beyond the relms of this Blog, link I totaly understand.
    Cheers Jeff for all your help

  42. THANK you for this. I was able to adapt your code very quickly to serve my needs exactly. I really appreciate your help!!!!!!

  43. Thanks for this code. I have a spreadsheet with three pivots filtered using Year and Month. The issue i have is that not all months are populated in each pivot table and when the code runs any pivot table where a particular month can’t be populated is defaulted to (All). Is there any code I can add to default this to (Blank) or “0” not (All)?
    Regards
    Richard.

  44. Hi Debra.
    I have the same question as May Lanie posted on June 27.
    I am using OLAP cube as the pivot table’s source and when I change a report filter in one pivot table, all the others pivot tables Pivot table change to “All”
    Can you please show us how to revise the code when OLAP cube is the data source?
    Thank you!
    Loshini

  45. First off, thanks for great info you share with the community. It’s helped me more than once.
    I have an Excel workbook with 3 pivot tables, all connected to an OLAP source.
    I’m using a couple of these tables to query the cube so I can copy data selected by the user to another workbook.
    Since I don’t want the users to alter the structure or filters in the tables, I’ve locked them down except for the page field on the first table. This allows them to select their project.
    My problem: I want to either prevent the user from selecting multiple items in this page field or at least warn them when they do, that the results will be wrong.
    I’m using an event procedure that fires when the user changes anything on the sheet:
    Sub Worksheet_Change(ByVal Target As Range)
    In this procedure, I’m trying this code to get/set this property:
    Dim bMult As Boolean
    ‘ check for users changing “select multiple items” box on page fields
    bMult = Sheets(“Sheet1”).PivotTables(“PivotTable1”).PageFields(1).EnableMultiplePageItems
    If bMult Then
    MsgBox “You have enabled multiple project selection which could lead to erroneous results!”
    End If
    ‘ Try to set the property
    ActiveWorkbook.Sheets(“Sheet1”).PivotTables(“PivotTable1”).CubeFields(1).EnableMultiplePageItems = False
    Both of these result in errors at the step where I try to read or write the property. I’ve tried PageFields, PivotFields, CubeFields, with no success.
    In the debugger, when I look at this property in the locals window, it always shows as False regardless of how it is actually set in the pivot table.
    Any ideas?

  46. Hi.
    This is a great thread full of good posts. My question is:
    I’ve got a pivot derived from an OLAP cube. I would like to create separate tabs based on one of the filters on the main page. Unfortunately, the Show Report Filter Pages in the Options drop menu won’t work with an OLAP cube. Does anyone know of a workaround for this? Also, there are multiple filters on the main page, and I’d like this capability to be associated with just one of the filters, for market.
    Thanks in advance for any help you might be able to provide.
    Chris

  47. Hi Debra, This is exactly what I have been looking for and if I can get it right will save a TON of time. I am trying to modify it so it only updates PT on a single workbook. I am having a hard time getting the code correct based on the comments you made to Becca on April 11, 2012. Here is the code I am using:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim wsMain As Worksheet
    Dim ws As Worksheet
    Dim ptMain As PivotTable
    Dim pt As PivotTable
    Dim pfMain As PivotField
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim bMI As Boolean
    On Error Resume Next
    Set wsMain = ActiveSheet
    Set ptMain = Target
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(pfMain.Name)
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = “(All)”
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt
    Next ws
    Next pfMain
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    1. Hi Debra, I have figure out my issue that I commented above. However, I am not sure if anyone else is having this problem, but when the macro runs the charts I have tied to the pivot tables looses some of its formatting. Specifically the ‘Plot Series On’ formatting. It resets the secondary axis back to the primary axis. Any thoughts?

  48. Hi, I use this with pivot tables from a database connection – I find that if I refresh the data connection, the pivot filters reset. My work around is to comment out the vba but is there any way around this?

  49. Great code and it’s been working perfect…until I had to move my report filter(Department) down to Row Labels
    What can I do to make this work? thank you

  50. Hi, I do an open refresh on all pivot tables and have used the program 4 times which I have to comment out and comment back in again to use the programs. I have various long solution. Do you know a quick way to have the programs running after an open refresh. The program is the master and slave program that does multi sheets pivot tables and multi Items. Amazing program I just can not start it after the refresh automatically !!
    Your expertise would be greatly appreciated.
    Private Sub Worksheet_PivotTableUpdate(ByVal target As PivotTable)
    SyncPivotFields2 target
    End Sub

  51. Sub Test6PTs() ‘ Original Code from the web by Debra Dalgleish
    Dim wsMain As Worksheet
    Dim ws As Worksheet
    Dim ptMain As PivotTable
    Dim pt As PivotTable
    Dim pfMain As PivotField
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim bMI As Boolean
    On Error Resume Next
    Set wsMain = ActiveSheet
    Set ptMain = PivotTable
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set pfMain = ptMain.PivotFields(“Route”)
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(“Route”)
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = “(All)”
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt
    ‘Next ws
    ‘Next pfMain
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

  52. Hello,
    Thank you for posting this insightful information. I was wondering is there anyway to “Change All Pivot Tables With One Selection” without using a macro?

  53. Thank you for posting this insightful information. I was wondering is there anyway to “Change All Pivot Tables With One Selection” for Excel 2007 without using a macro?
    ..

  54. Denet…if you’ve got excel 2010 you can use slicers, which effectively let you do the same thing without any VBA whatsoever.
    On the downside,
    1. slicers can take up quite a bit of screen real-estate (although if you have the Microsoft PowerPivot addin installed, they are much improved).
    2. slicers only work with pivots that share the same pivot cache.
    But slicers are very fast.

  55. This is awesome, I’ve implemented this approach on several reports. Works great in the MS version, but anyone know how to make this work on a Mac (running Excel 2011)?

    1. I’m on Mac (Excell 2011) and the sample doesn’t work either. I turned slicers to false (bUseSlicers = False) since Excel for Mac doesn’t support them, but alas no luck.

      1. Drew: I have another version somewhere that should work. Just need to find it. Email me at weir dot jeff at gmail dot com and I’ll see if I can track it down for you.

  56. I can’t seem to get the code to work on excel 2003. I created two pivot table on the same worksheet called DRG. Then I right clicked the worksheet, view code, and copied to pivottable update subroutine. I removed update all worksheet codes.
    That is it right? but nothing happens to the second pivot table when I change the first pivot table filter.
    Am I missing something?

  57. Thanks for this code; it’s excellent! I have the same question as Richard from October.
    In my 2010 spreadsheet, I have three pivot tables that all contain “regions”. The same region numbers do not appear in each of the three pivot tables. Therefore, when I select a region (say “2”) PT1 and PT3 may have that value, but since PT2 does not, I see (All) entries. Is there a way for the tables that don’t contain the selected data to show (blank)?
    Thank you!
    Lance

  58. @Lance: Add somne dummy lines in your source data…one for each region. That way, each region will ALWAYS appear in each pivot table. That should solve your problem.

  59. @J: Re your comment , I use this with pivot tables from a database connection – I find that if I refresh the data connection, the pivot filters reset. My work around is to comment out the vba but is there any way around this?
    Because filters don’t persist between refreshes, one way i’ve got around this in the past is to do a refresh, and then refilter the pivots with code. E.g. something like this:

    With Sheet9.PivotTables("NPV View").PivotFields("Cost Type")
    .PivotItems("Depreciation").Visible = False
    .PivotItems("Capital Charge").Visible = False
    End With

  60. Hi,
    first of all massive thanks for sharng this….this came in soooo handy for various reports.
    But I now have a slight problem with it. I have a report with two pivots on one tab……I have a line graph from one of the pivots. I added a secondary axis to this graph but I lose it everytime a change a pivot table fitler (ie it reverts back to one axis)
    I should add that if change the filter/pivit that the chart drives off, everyhting is fine,,,,it’s when I change the fitler/pivot on hte other pivot table that I lose the seconday axis.
    Can you offer any help (bit of a mouthful I know)? Not sure what’s causing this and therefore what to do to rectify.
    yours in hope (adn thanks a millions again)
    Dave

  61. David: Can you share a file of this with non-confidential data?
    Also, if you’re using Excel 2010 or greater, do you get the same behavior if you use Slicers?

  62. This code rock thanks so much! And I know you are busy but I have a problem with the code. It works great when I select one item at at time, but if I select multiple items the other pivot tables will not update. I would really appreciate any help on this one, and thanks so much for what yall do!!!! 🙂

  63. Thanks so much for sharing the code! I have 4 pivot tables, each having 6 filters. There are 5 values in Filter #1. When I choose Value #1, #2 and #3, all 4 pivot tables change value. However, when I choose Value #4 or #5, the other pivot tables don’t change value with the first pivot table. The other filters seem to work alright. I have ~200,000 lines in the raw data. What may cause the problem and how to solve it? Thanks!!

    1. When I cut the # of lines down in the raw data, it worked fine for me. I was wondering if Excel or the code has limitation on the amount of raw data to process.

      1. I don’t have slicer. I tried the new code, but I got an error message “Application-Defined or Object-Defined Error.” it happens when i choose value 4 and 5 for one of the filterss. other filters seem to work fine.

      2. Cindi – couple of questions for you:
        1. What version of Excel are you using
        2. Do all the pivot tables share the same data source?
        3. What are the values 4 and 5 for the filter that doesn’t work? Are they text, or numbers?
        4. Are you sure that the values for 4 and 5 actually appear in the other tables?

  64. Hey Jeff,
    Thanks so much for the quick reply!! I tried the updated code and the same thing happened. When multiple items are selected on one pivot table, the others do nothing. But again even if I can’t get this figured out, the code has save me much time!!!

  65. Hi
    I am using this code in two different sheets.( So one table in sheet 1 and 9 other tables in sheet 2. when i filter for any field the Macro works fine and applies the filter to all the pivot tables but its very slow. Its takes around 15 Seconds to update all the pivot. Is this normal or can it be made work faster?

  66. Pingback: Changing Multiple Pivot Tables At Once | A Digital Marketing Blog
  67. Thank you Jeff. It worked. I have become a great fan of this blog.
    I have one more issue. I am using the following code to link pivot filter to data filter in a sheet for column “Region”. This code works perfectly fine but does not work for multiple region selection. Need Help to make it work for multiple filter value selection
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim fieldtocheck As String
    Dim updatedsheetname As String
    Dim thispivotname As String
    Dim col As Integer
    Dim column_region As Integer
    Dim filterrange As String
    ‘parameters
    updatedsheetname = “Pricing log data”
    thispivotname = “PivotTable1”
    column_region = 6
    filterrange = “$A$3:$CI$176”
    ‘update Region …
    fieldtocheck = “Region”
    col = column_region
    If (ActiveSheet.PivotTables(thispivotname).PivotFields(fieldtocheck).CurrentPage = “(All)”) Then
    Sheets(updatedsheetname).Range(filterrange).AutoFilter Field:=col
    Else
    Sheets(updatedsheetname).Range(filterrange).AutoFilter Field:=col, Criteria1:=ActiveSheet.PivotTables(thispivotname).PivotFields(fieldtocheck).CurrentPage
    End If
    End Sub

  68. @Billy…sorry for the slow response…I missed your last comment somehow.
    I’m not sure why this isn’t working for you. What happens when you run the code in the sample workbook as is…i.e. the workbook at http://www.contextures.com/excelfiles.html#Pivot called PT0029? Does it work for multiple items in that workbook ? Or does that fail too?
    WHat is the source for your pivottable? Is it from an OLAP data source by any chance? (I’m not sure if this matters…just trying to get a handle on what might be going on.)
    Can you post a non-confidential version of the workbook somewhere and post a link here?

  69. @Raghu…so you want to filter a table based on what a pagefield pivotfilter is set to?
    In your 2nd to last line, you are trying to use the .CurrentPage setting as the filter criteria. The .CurrentPage property will only ever return one string:
    * If your pivottable pagefield has the “Select Multiple Items” option checked, then the .currentPage property ALWAYS returns “(ALL)”. So this property is basically irrelevant and useless in this case.
    * If your pivottable pagefield does NOT have the “Select Multiple Items” option checked, then the .currentPage property ALWAYS returns either the name of the currently selected item, or (if all items are selected) it returns “(All)”
    So your approach isn’t going to work.
    Instead, I suggest you set up another pivot on a hidden sheet, with just the Region column in it as a page field, and use my revised code to sync this pivot so that it always reflects the choice in the master pivot. Then use that as the criteria for your code above. So the Criteria bit in that last line would be something like:
    Criteria1:=SheetX.PivotTables(“PivotTableX”).PivotFields(“Region”).datarange.
    Give this a try, and if you have any issues post back here.
    Cheers

  70. This program is close to the solution I require.
    Can you be so kind as to advise upon the following please :
    1. I Open my workbook and it refreshes all pivot tables on different worksheet.
    2. My pivot tables are set to month and year and I would like them to remain this way e.g. May-2013, Jan-2013, etc
    2. the program listed above needs to keep the item values on the refresh when opening the workbook yet when a change is made to one pivot item on after the opening refresh it will then change.
    a) Open workbook with refresh for all pivot tables.
    b) When worksheet with the two pivot tables is refresh via the open it does not change from May-2013 to All.
    c) When a user goes to the worksheet and manaully changes the date from May2013 to June2013 the sync program will exicute.
    I can use the same program for different worksheets. 3 worksheets to do in total. therefore 3 programs as the criteria sync needs to be different (unique)
    One little part to solve then all is completed.
    Thank you for your time in reading this and hopefully providing me with a complete solution.

  71. Ian, If I understand your question correctly, it sounds like you are saying that:
    1. the pivots get automatically refreshed on workbook open.
    2. this refresh event then triggers the above code, which you do NOT want to happen. Rather, you ONLY want the code to run when a user manually triggers it.
    Is that correct?
    If that’s the case, then perhaps you could make sure that the ‘Refresh data when opening the file’ option is NOT checked. In Excel 2010, you can access that by right clicking on the pivot table, then selecting PivotTable Options, then selecting the Data tab and unchecking the ‘Refresh data when opening the file’ tickbox.
    Otherwise can you please clarify your question a bit more?

  72. Hi Jeff,
    I have all pivot tables not to refresh automatically and have a refresh called when opening the spreadsheet. over 100 pivot tables on 30 sheets needed to be refresh when opening. Yes a large Dashboard
    Also I have 9 pivotables on different connections and showing different data except they have the pivot fields the same. hence this program is ideal as a solution to syncing them.
    1. I need to be able to open the spresheet refresh the data and then the program starts to work. It is running on refresh and setting the dates to all. Date always needs to be set to a month. e.g. this month and can be changed. 1 pivot tables being the control for the other 8 pivot tables.
    If I have SyncPivotFields2 target (lined out) and then switch on after the refresh. then the pivot tables sync and it works fine. as a user pinot of view I would like the pivot tables not to change on opening and refresh then after all the data is loaded then the sync program working. This is the final part to my dashboard that I have been working on over the past 2 years so this is the final part to go in place. Your advise experience is appreciated.
    Yes only when manually changed by a user not when a module is refreshing. Any ideas or even a solution would be welcome.
    Start Program
    Sub ActivatePivotTablesData()
    Application.AlertBeforeOverwriting = False
    Application.DisplayAlerts = False
    ActiveWorkbook.RefreshAll
    End Sub
    Trigger Program
    Private Sub Worksheet_PivotTableUpdate(ByVal target As PivotTable)
    SyncPivotFields2 target
    End Sub
    The Long Sync Program (have also tested with the smaller version listed above – same results)
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim TimeTaken As Date
    TimeTaken = Now()
    Dim pf_Master As PivotField
    Dim pt_Master As PivotTable
    Dim pi_Master As PivotItem
    Dim bPageField As Boolean
    Dim bFiltered As Boolean
    Dim bUseDictionary As Boolean,
    For Each pf_Master In pt_Master.VisibleFields
    If pf_Master.Orientation = xlPageField Then
    Select Case pf_Master.Name
    etc………..

  73. Okay, a couple of thoughts.
    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.
    Also, what version of Excel do you have? The reason I asked this is that I’ve just sent Debra some code that users slicers for pivots that share the same cache, and uses my dictionary-based routine that I posted at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/ for any pivots that don’t share the same cache. Sounds like this will be perfect for your situation, but slicers require Excel 2010 or later. She’s going to post it soon.
    Lastly, if for any reason my approach doesn’t work, another option is to have a ‘sync pivots’ button on the spreadsheet, so that my code only gets run when users push the button.
    Let me know if this fixes your problem.

  74. Hi Jeff thank you for your reply. I will test upir solution today.
    I am using Excel 2010.
    Splicer are great if the connections are the same. I have two different DB’s therefore the connections are different. These programs are the best I can find on the internet as the solution I require.
    In relattion to your interest on the Dashboard.
    I compile Statisitcal Data in Excel 2010.
    1. Collect Data from the source in CSV format.
    2. Add data into Access DB’s
    3. Connect to other Access DB’s (Linked Tables)
    4. Dashboard running on schedules to load and count email boxes receiving data.
    Compile all this in Excel Data files, Pivot tables and Excel Tables (the latter for flexability in making Excel Charts)
    Finally having automated programs to assist in the front end desgin of excel via VBA also mini programs such as list folder and files for daily IT duties.
    You are welcome to any of the programs I have from my Excel Dashboard.
    Sub Statistics()
    Application.AlertBeforeOverwriting = False
    Application.DisplayAlerts = False
    Dim objOutlook As Object, objnSpace As Object, objFolder As Object
    Dim EmailCount As Integer
    Set objOutlook = CreateObject(“Outlook.Application”)
    Set objnSpace = objOutlook.GetNamespace(“MAPI”)
    On Error Resume Next
    Set objFolder = objnSpace.Folders(“MailboxName”).Folders(“Inbox”)
    If Err.Number 0 Then
    Err.Clear
    MsgBox “No such folder.”
    Exit Sub
    End If
    Dim iCount As Integer, DateCount As Integer
    Dim myDate As Date
    EmailCount = objFolder.Items.Count
    DateCount = 0
    myDate = Sheets(“Statistics”).Range(“A2”).Value
    For iCount = 1 To EmailCount
    With objFolder.Items(iCount)
    If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCount = DateCount + 1
    End With
    Next iCount
    Set objFolder = Nothing
    Set objnSpace = Nothing
    Set objOutlook = Nothing
    Sheets(“Statistics”).Select
    Range(“A1”).Value = DateCount
    ‘End of Email count code

  75. Jeff.
    Replacing ActiveWorkbook.RefreshAll
    With :
    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
    IT HAS WORKED. I have done this in all VBA programs in the Dashboard and initial testing confims I now have a fully automated and now syncronised DASHOBOARD.
    Many Many Thanks. Ian.
    Let me know if you need any VBA programs, etc. Also willing to help upon request.

    1. Hi Ian,
      I am running into the same problem you have had. I am trying to sync pivot table filters across 80+ pivot tables on one worksheet. However, the pivot tables are pulling data from an external cube and not all of the tables are pulling from the same source. Jeff has tried to help me resolve this issue but I am still having a lot of trouble. If you could provide any insight on this it would be greatly appreciated.
      Thanks,
      Steve

      1. Steve – I believe the difference is because your pivots are for OLAP data, while Ian’s were not for OLAP. My code doesn’t (yet) handle OLAP, primarily because I don’t have any OLAP datasources to play with. If you can record some code where you set up a slicer on an OLAP datasoure and then send to me, that would be great.

  76. Hi there! Love this – quick question for you though… (Excel 2007) will this work to update all of a specific filter that is date related? Here’s my situation – I have a huge dataset. I have multiple pivot tables on multiple sheets to help me provide multiple reports,etc. One constant that I have is that I update all pivots to have the field called “WkEnding” as Greater than . For example, this week it was Greater Than 3/31/13. SO… ON some tables it is a column filter, on others it is a row filter.
    Will this work to update all of these?
    Thank you!! You are awesome!

    1. Let me change up the question real quick, because I had to set the report aside for a while & i am just now jumping back into it….
      On a particular worksheet within the workbook I have a bunch of pivot tables. The column header is “Wk Ending”. This is the item that I need to filter. I re-pulled code from here and tried it just now. When I update the “Wk Ending” field all of the other pivot tables completely lose the filter. They show all dates. Here is the code I am using. Can you help me out? Thank you so much!!
      Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
      On Error Resume Next
      Dim wsMain As Worksheet
      Dim ws As Worksheet
      Dim ptMain As PivotTable
      Dim pt As PivotTable
      Dim pfMain As PivotField
      Dim pf As PivotField
      Dim pi As PivotItem
      Dim bMI As Boolean
      On Error Resume Next
      Set wsMain = ActiveSheet
      Set ptMain = Target
      Application.EnableEvents = False
      Application.ScreenUpdating = False
      ‘change only Region field for all pivot tables on active sheet
      Set pfMain = ptMain.PivotFields(“Wk Ending”)
      bMI = pfMain.EnableMultiplePageItems
      For Each pt In wsMain.PivotTables
      If pt ptMain Then
      pt.ManualUpdate = True
      Set pf = pt.PivotFields(“Wk Ending”)
      bMI = pfMain.EnableMultiplePageItems
      With pf
      .ClearAllFilters
      Select Case bMI
      Case False
      .CurrentPage = pfMain.CurrentPage.Value
      Case True
      .CurrentPage = “(All)”
      For Each pi In pfMain.PivotItems
      .PivotItems(pi.Name).Visible = pi.Visible
      Next pi
      .EnableMultiplePageItems = bMI
      End Select
      End With
      bMI = False
      Set pf = Nothing
      pt.ManualUpdate = False
      End If
      Next pt
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End Sub

  77. @maritrack – if you’ve got Excel 2010 or later you could use slicers to do this with no code. Slicers do take up a bit of extra screen real estate, but they are very easy to set up, pretty much bullet-proof, and very very fast. See http://blog.contextures.com/archives/2011/03/07/filter-multiple-pivot-tables-with-excel-2010-slicers/
    Or you could use my code from http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ that uses slicers to sync any pivots that share the same pivotcache, and another approach for pivots that don’t share the same cache (or if you don’t have Excel 2010 or later).
    This code lets you specify:
    1. any sheets you DON’T want the macro to check
    2. any specific pivot tables that you DON’T want the macro to synchronize.

    1. Actually, this didn’t quite work for me since all of my pivots are on the same sheet. I am using 2007, so cannot use slicers. Is there a way to ignore certain pivot(s) using the code posted above? thanks!

  78. Maritrack: It shouldn’t matter whether your pivots are on the same sheet or not. And if I’ve programmed it correctly, the code should work even in Excel 2007 (although in that case it won’t use slicers, it will use an alternate and slightly slower approach instead).
    I suggest you download my code from http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ and try it as is on your 2007 system. If it works, then we know that the code will also work on your specific dataset with a few tweaks.

  79. OK – I tried different codes and was able to get one of them work. It’s the one posted here – PivotMultiPagesChangeAllVar.zip – (the one that changes only one field for all pivot tables on active sheet). This actually helps me since I only want one particular field (report date) to be updated in bulk. I couldn’t figure out how to use the macro you suggested (I am very new to macros and vba programming,…). So my code looks like this and updates the ‘Report Date’ field on all pivot tables on my sheet. Ideally, I would like my pivot tables be updated only if one particular pivot table is updated. Is it possible to make it happen using this code? I think I would need to set the name for ptMain., but again, I know so little about this so I am just getting lost. I appreciate your help very much!
    set pfMain = ptMain.PivotFields(“Report Date”)
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(“Report Date”)
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = “(All)”
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt

  80. I am using the “This_Sheet_All_Fields” code, but need to adapt it so it works for Row Labes instead of filter fields. Is that possible?

  81. I am using Excel 2007. I need to use the “This_Sheet_All_Fields” code, but for the Row labels instead of Report Filter Fields. Is that possible? If so, please assist with modifying the code.

  82. Hi,
    I’m trying to synchronize multiply slicers with multiply data sources:
    Sub Sync_MBFL_to_BPL_series()
    Application.ScreenUpdating = False
    Dim SLI As SlicerItem
    Dim SLC_BPL_series As SlicerCache
    Dim SLC_MBFL_series As SlicerCache
    Set SLC_BPL_series = ActiveWorkbook.SlicerCaches(“Slicer_Belt_series”)
    Set SLC_MBFL_series = ActiveWorkbook.SlicerCaches(“Slicer_Belt_series1”)
    SLC_BPL_series.ClearManualFilter
    On Error Resume Next ‘On error set to false
    For Each SLI In SLC_MBFL_series.SlicerItems
    If SLI.Selected = True And SLI.HasData = True Then SLC_BPL_series.VisibleSlicerItems(SLI.Name).Selected _
    = True Else SLC_BPL_series.VisibleSlicerItems(SLI.Name).Selected = False
    Next
    Application.ScreenUpdating = True
    End Sub
    Above script is working, except that sliceritems not present in SLC_MBFL_series will not be turned off in SLC_BPL_series. Any suggestions???
    Thanks in advance
    JHN

  83. jeffreyweir – The situation is that I will always have more sliceritems in SlicerCaches BPL then slicercaches MBFL, meaning that using “For each” function on the SlicerCaches MBFL some sliceritems will never be turned off / checked by the “For each” function.
    Do you know how I could check if sliceritems in SlicerCaches BPL are present in SlicerCaches MBFL? and if not then turn them off? (How would that code look like?)
    Thanks in advance

  84. Also, do you only want the changes to flow one way, i.e. if you alter the visible fields in slicer/pivot A, the slicer/pivot B changes accordingly, but not the other way around.
    Or do you want it to flow both ways? i.e. whichever slicer you use, the changes flow to the other pivot.

  85. jeffreyweir – Appr 300 items in the master and appr 500 items in the slave.
    Let me put it this way:
    – a flow one way is need to have (and I can trigger the script manually).
    – a flow both ways would be nice to have (also here I can trigger the script manually).

    1. JHN: Because Slicers generate a Pivot Update event, you should be able to use my code posted at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ to sync the other pivot, which will also effectively sync the other slicer.
      But I’ve just discovered a bug in that code if there is already a slicer in the workbook. Let me fix that bug, and see if the code then will do what you need it to. Might be a couple of days.

  86. Jeff – I hate to ask – but I had posted a few weeks back, and I’m hoping you can help me. 🙂 I’ve been trying to figure out what I need to change in the code, but here’s the situation – the field I need to change, “Wk Ending” is a column header. When I update it while I have this code, it loses all filters in all the pivot tables on that sheet. Can you help me? Again, this is a column header that is a date, and when I update it, it is usually with me using a date filter similar to After 5/13/19.
    Hoping you can help. I feel really lost. 🙂 Thank you!
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    On Error Resume Next
    Dim wsMain As Worksheet
    Dim ws As Worksheet
    Dim ptMain As PivotTable
    Dim pt As PivotTable
    Dim pfMain As PivotField
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim bMI As Boolean
    On Error Resume Next
    Set wsMain = ActiveSheet
    Set ptMain = Target
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ‘change only Region field for all pivot tables on active sheet
    Set pfMain = ptMain.PivotFields(“Wk Ending”)
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(“Wk Ending”)
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = “(All)”
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    1. Hi Shannon. Sorry I missed your previous reply. Probably best you post this question on either the excelguru forum or the Chandoo forum (google will lead you to either) and upload a sample file there, as this might take quite a bit of back and forth to get to the bottom of.

  87. Jeff-First off I am a novice. Consider me wearing floaties. I am using the code from the PivotMultiPagesChangeSet2010 file to run against a file that has multiple worksheets with pivot tables from a data cube. When I change one worksheet it sets all the others back to unfiltered or a value of “All” so I am thinking it has to do with the bMI parts of the code and charateristics of the cube maybe. I am on 2010 but I have users that will use this file that will be on 2007. Can I do this with data cube files?

  88. @JHN: I’ve realised that you can use my code posted at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ with a very small modification to do what you need. Download that book, copy the code to your workbook, and in the module called modSyncPivotsAnyVersion, look for this line near the top of the routine:
    bUseSlicers = True ‘Here’s where we can manually force code to NOT use slicers, by setting this to False
    Just replace that TRUE with a FALSE, and you should be good to go.
    Note that when you are copying the code across, you also have to put this in each workbook module with pivots in it:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    SyncPivotsAnyVersion Target
    End Sub

  89. Hello,
    I just wanted to check again to see if the below was possible.
    Le’Nae
    August 5, 2013 at 12:38 pm · Reply
    I am using Excel 2007. I need to use the “This_Sheet_All_Fields” code, but for the Row labels instead of Report Filter Fields. Is that possible? If so, please assist with modifying the code.

  90. jeffreyweir – I actually already tried that, but still not doing 100% what I need. (Cool code anyway).
    I do have 2 different data sources (Source A & Source B).
    Source A contains 20 slicers, source B contains 13 slicers and both do have 4 slicers in common (Let’s say Master-slicer 1, 2, 3 &4).
    Example: From source A with 20 slicers, I do make selections on slicer 7, 13,14 and 18 ==> with impact/filtering of Master-slicer 1, 2, 3 and 4.
    So now I need the Master-slicer from both sources to be in sync.
    Maybe a cryptic explanation, anyway I could send you a copy of my file then it would be clear?
    PS. You have already been a great help, thanks in advance.

  91. I have two pivot tables that run off the same cache. The code in the base of this post works great for aligning both pivot tables to the same report filter, but when I filter one of the pivot tables using a slicer (Which is connected to both pivot tables) the other pivot table defaults the report filter to all. What adjustments can I make to the code to keep the report filter selection and slicer selection constant throughout both pivot tables.

  92. Hi Jeff, sorry, I´m using the PivotMultiPagesChangeSet2010 code on my pivot tables but when filtering, it includes the blank filter along with the one I needed. Is there a way to avoid this?

  93. I used the code to change all Pivots successfully and I was very happy 🙂 that it worked as I had over 30 worksheets.
    However, I added external data sources linked to SharePoint and wrote a Macro to perform a refresh and after the macro runs the VBA code no longer works. I added a Test Message and during the refresh the Vba code get’s called, but does not work afterwards.
    I am not a VBA programmer I developed an elaborate Excel Dashboard that requires external data refresh, any ideas as to why the VBA code gets nullified.
    Your assistance is appreciated.
    Also it would be ideally if I can call this “change all pivots” code as a macro on demand as needed.

  94. I must say that I love this code. However I ran into a small problem when I had cells with in my pivottables that are empty. I do have a few of empty cells since information isnt needed in all fields when its filled into the source.
    The error code is 13 and its this line that causes it: If pi_Master.Visible Then ‘add any visible pivotitems to our master list
    I also got another error, which I dont know the reason to. its error 91 and its caused by this line: Set slrField = sc.Slicers.Add(ActiveSheet). If I understand it correct its some variable that its missed to define. I belive this is due to my misstake, when setting up the excel sheets.
    /T

  95. Torbjorn – I take it you are using my code posted at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ ?
    If so, then you may well get this error message in the case that you use slicers in your workbook. I’m aware of this issue, and am recoding that routine so that it doesn’t error out in that case. I’m also planning to address the other issue you mention.
    Flick me an email at [email protected] so I have your return email address, and I’ll send the amended code to you when I’m finished redeveloping it.

  96. I am such a newbie and WAY over my head – but always have found help on this board. This routine is one that I need, however as another macro is creating a series of Pivot Tables a single targeted tab named REPORT, I need to understand how to modify the code so that it can be called up from the same module. From my existing Code, I am using: Call Control
    Nothing errors – but nothing happens after I edit a field entry. Based on an question and reply in on this blog, I have removed reference to the “For Each ws..” and “Next ws” lines, then changed the first few lines to the following:
    For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    The code I am using follows; what am I doing wrong??
    Sub Control()
    Dim wsMain As Worksheet
    Dim ws As Worksheet
    Dim ptMain As PivotTable
    Dim pt As PivotTable
    Dim pfMain As PivotField
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim bMI As Boolean
    On Error Resume Next
    Set wsMain = ActiveSheet
    Set ptMain = Target
    ‘Application.EnableEvents = False
    ‘Application.ScreenUpdating = False
    For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(pfMain.Name)
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = “(All)”
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt
    Next pfMain
    ‘Application.EnableEvents = True
    ‘Application.ScreenUpdating = True
    End Sub

  97. I was able to modify a good portion of the code in the “SyncPivotsAnyVersion” function to work with OLAP data cubes. You can still specify worksheets and pivot tables to exclude, but it does not have the ability to exclude pivot fields. Also, it only syncs the page fields (which i believe are all the fields that are in the report filter section of the pivot table). I’m fairly new to VBA so take my code below for what it is and know that it’s likely not the best method. I usually don’t post on sites like this but it seemed like there were a lot of people struggling with this.
    Option Explicit
    Sub SyncPivotsAnyVersion(Target)
    ‘ Revised by Lars, November 2013 to work with OLAP cubes
    ‘ Originally Downloadeded from http://www.contextures.com
    ‘ Revised code by Jeff Weir, June 2013
    ‘ Contact [email protected] or [email protected]

    ‘ Description: Select an item in a pivot table’s page fields, and
    ‘ page fields for other pivot tables in workbook will
    ‘ change to the same Item.This macro sets whatever is selected in the
    ‘ In the code, you can specify
    ‘ * any worksheets to be ignored; and
    ‘ * any pivot tables to be ignored.

    Dim pt_Master As PivotTable
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    ‘CPL stands for CurrentPageList
    Dim bCPL As Boolean ‘This boolean gets used to tell the loop when to exit
    Dim CPL_Numbers_Array() As Long ‘This array lists the number if items in the CurrentPageList for each PivotField
    Dim CPL_String As String ‘This string will contain all items in the CurrentPageList
    ‘and those items will be separated by semicolons
    Dim CPL_String_Array As Variant ‘This array contains all items in the CurrentPageList
    Dim varExcludePivots As Variant
    Dim varExcludeSheets As Variant
    Dim varTest As Variant
    Dim lng As Long
    Dim strExclusions As String
    Dim bDoNothing As Boolean
    Dim i As Integer
    Dim J As Integer
    ‘Set to false to prevent Excel event handlers from being called
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set pt_Master = Target
    ReDim Preserve CPL_Numbers_Array(pt_Master.PageFields.Count – 1)
    ‘specify any WorkSheets that you DON’T want to check
    ‘in section marked with ‘*******
    ‘specify any PivotTables that you DON’T want to change (but that are in WorkSheets that you DO want to check)
    ‘in section marked with ‘=======
    ‘**********************************************************************************
    ‘ Here’s where we list any WorkSheets that we want the code to skip
    ‘ We simply list them below like so (without the apostrophe ‘ at the front):
    ‘ strExclusions = strExclusions & “PUT WORKSHEET NAME HERE;”
    ‘ For this particular example, we will tell Excel to only ignore the
    ‘ worksheets called ‘Region Reports’, ‘Pivot Data’, and ‘MyLinks’
    strExclusions = strExclusions & “Notes;”
    strExclusions = strExclusions & “Summary;”
    ‘**********************************************************************************
    If strExclusions = “” Then strExclusions = “;”
    varExcludeSheets = Split(strExclusions, “;”)
    strExclusions = “”
    ‘———————————————————————————–
    ‘ Here’s where we list any PivotTables that we want the code to skip.
    ‘ We simply list them below like so (without the apostrophe ‘ at the front):
    ‘ strExclusions = strExclusions & “WORKSHEET NAME_PIVOTTABLE NAME;”
    ‘ Say we want to ignore PivotTable 1 on a sheet called Pivots as well as
    ‘ as ignore PivotTable 6 on a sheet called Graphs.
    ‘ We simply list them like this
    ‘ strExclusions = strExclusions & “Pivots_PivotTable 1;”
    ‘ strExclusions = strExclusions & “Graphs_PivotTable 6;”
    ‘ For this particular example, we will tell Excel to ignore PivotTable3 on
    ‘ the Other Pivots sheet
    strExclusions = strExclusions & “Other Pivots_PivotTable3;”
    ‘————————————————————————————-
    If strExclusions = “” Then strExclusions = “;”
    varExcludePivots = Split(strExclusions, “;”)
    strExclusions = “”
    ‘ Check if Master Pivot Table is either:
    ‘ 1. one PivotTables that we want the code to skip
    ‘ 2. in a worksheet that we want to skip
    ‘ If it is, we do nothing
    varTest = Application.Match(pt_Master.Parent.Name & “_” & pt_Master.Name, varExcludePivots, 0)
    If Not IsError(varTest) Then bDoNothing = True
    varTest = Application.Match(pt_Master.Parent.Name, varExcludeSheets, 0)
    If Not IsError(varTest) Then bDoNothing = True
    If Not bDoNothing Then
    ‘ Add the master pivot to the list of pivots to exclude
    lng = UBound(varExcludePivots) + 1
    ReDim Preserve varExcludePivots(1 To lng)
    varExcludePivots(lng) = Target.Parent.Name & “_” & Target.Name
    For Each pf In pt_Master.PageFields
    bCPL = False
    i = 0
    ‘This is the CPL_Loop
    Do
    ‘Since we can’t count the CurrentPageList, we have to iterate
    ‘through it and use the Error handler to handle runtime errors
    On Error GoTo Err1:
    If CPL_String = “” Then
    CPL_String = pf.CurrentPageList(i + 1)
    Else
    CPL_String = CPL_String & “;” & pf.CurrentPageList(i + 1)
    End If
    i = i + 1
    CPL_Loop:
    Loop Until bCPL
    CPL_Numbers_Array(pf.CubeField.Position – 1) = i
    Next pf
    CPL_String_Array = Split(CPL_String, “;”)
    For Each ws In ThisWorkbook.Worksheets
    ‘Check to see if worksheet is listed in the “Excluded Worksheets” list
    varTest = Application.Match(ws.Name, varExcludeSheets, 0)
    If IsError(varTest) Then
    ‘It’s not in the list, so go ahead
    For Each pt In ws.PivotTables
    ‘Check to see if PivotTabe is listed in the “Excluded Pivots” list
    varTest = Application.Match(ws.Name & “_” & pt.Name, varExcludePivots, 0)
    If IsError(varTest) Then
    ‘It’s not in the list, so go ahead
    pt.ManualUpdate = True
    ‘Iterate through each PageField and set the items to those
    ‘items selected in same PageField of pt_Master
    i = 0
    For Each pf In pt.PageFields
    If CPL_Numbers_Array(pf.CubeField.Position – 1) = 1 Then
    With pf
    .AddPageItem CPL_String_Array(i), True
    i = i + 1
    End With
    Else
    J = 1
    With pf
    .AddPageItem CPL_String_Array(i), True
    i = i + 1
    Do
    .AddPageItem CPL_String_Array(i)
    i = i + 1
    J = J + 1
    Loop Until J = CPL_Numbers_Array(pf.CubeField.Position – 1)
    CPL_Loop2:
    End With
    End If ‘If CPL_Numbers_Array(pf.CubeField.Position – 1) = 1 Then
    Next pf
    End If ‘If IsError(varTest) Then *Excluded pivot table check
    pt.ManualUpdate = False
    Next pt
    End If ‘If IsError(varTest) Then *Excluded worksheet check
    Next ws
    End If ‘ If Not bDoNothing Then
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    Err1:
    If Err.Number = 9 Then
    bCPL = True
    ‘Returns to the CPL_Loop label in the above code
    Resume CPL_Loop:
    End If
    If Err.Number = 1004 Then
    ‘Returns to the CPL_Loop2 label in the above code
    Resume CPL_Loop2:
    End If
    MsgBox Err.Description, vbCritical, “Whoops, something went wrong…”
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    1. I found out today that the code I posted only works if all the pivot tables page fields have the “Select Muliple Items” checkbox checked. Otherwise the CurrentPageList will show up as “” and my iteration through the page fields does not work

  98. Debra.
    This macro works perfect…..I love it but i will like to make one adjustment. i am using it on an excel sheet that has multiple pivot tables in multiple work books. Using your sample, I will like to change filter Region on the page and it updates in all the pivot tables, but i will also like my user to change item, date or employee on one pivot table without affecting other pivot tables…..Regeion is the ONLY filter i want to affect ALL the pivot tables in all tabs…..

    1. @Jeff
      I tried your code but i got the same thing…..the Region changes in all the sheets but when i change item or employee on on pivot table it affects all the pivot tables.I only what Region to change in all pivot tables.

  99. Ok. In that sample file there are instructions on how to amend the code so that it ignores any fields you don’t want to sync (in your case, item or employee). Look at the callout box near PivotTable4 in the ‘Other Pivots’ tab for an example on how to do this.

    1. Jeff, i check it again and it works fine. THanks…I have one more question. Now that my selected Region shows on ALL pivot table, if i wanted to lock report filter to certain pivot tables also Region changes, is that possieble. For example, my region selected is Quebec, but i want the filter on pivottable to to be locked on Employee: Gill and Pivottable 3 i want the filter locked on Item:Desk and pivot table 4 locked on the filter “1/6/11″….but all the Region remains Quebec…..is that possible.

      1. Won’t the existing code do that? Just add the Employee field and the Item field to the exclusions list, so that those fields don’t get synchronized. Or am I misunderstanding you?

  100. Code works flawlessly,however, have an issue when I refresh data for the pivots. The issue is this: When I refresh the pivots the filters filter to some random selections eventhough each filter is filtered to “All” before I refresh. Any ideas why or how to fix so that upon a refresh the filters will keep “All” selected?

  101. Jeff, I should also probably clarify that I am using the code posted at the top of this blog under the how it works section.

  102. Rob: First, try the code at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ because it may fix the issue (and even if it doesn’t, it’s substantially faster).
    If that doesn’t work, you could put this macro into a worksheet code module for a sheet that contains a Pivot, and then add a button on the worksheet with the title ‘Reset’ and make the button trigger the macro:
    Sub SetToAll()
    Dim pt As PivotTable
    Dim pf As PivotField
    Set pt = ActiveSheet.PivotTables(1)
    For Each pf In pt.PivotFields
    pf.ClearAllFilters
    Next
    End Sub

  103. Jeff,
    I downloaded the sample file PT0029 from the link you provided, The code is very minimal in this workbook and doesn’t seem to work for me. I assume that you mean to use this code:
    Option Explicit
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    SyncPivotsAnyVersion Target
    End Sub
    First, I have enabled macros. If I make filter selections in one pivot in your file it doesn’t change any other pivots. And if I use the code in my workbook I get the following error. Complie error Sub of function not defined.
    Thanks for you time Jeff!

  104. Hi Rob. That’s just the ‘trigger’ that sits in a sheet module. The actual ‘gun’ is the code in the three Code Modules called modGeneral, modSlicers, and modSyncPivotsAnyVersion.
    Drop me an email on [email protected] if you can’t see this, or if the code doesn’t play well with Excel 2007, and i’ll help you out.

  105. I have a question, how can I change this code to change the filter on the “Row Labels” of the pivot tables. The code works great, but only if I change a filter from the “Report Filter” field list. I have 44 pivot tables on two sheets. I would appreciate your prompt response.

    1. Hi Jeff,
      Thanks for the great piece of code. It works flawlessly in Excel 2010, but it does not work in Excel 2007; even if I set the “Slicer” part of the code to “False”.
      Asad Hamdani.

      1. Asad…I’ve just worked out why this is, and will email you the revised code once I’ve had a chance to test it. And then will ask Deb to upload my updated code.

  106. Has anyone solved the issue of using pivots based on OLAP cubes instead of regular pivots? This is an excellent and succinct macro, but I would love to have a version designed for OLAP cubes.

  107. This is an excellent example fo linking pivot tables. Thanks.
    This is very close to what I need. I need to link both “page fields” as is done in this example, and also “row labels” across several pivot tables. In other words, I also want to select row labels once and have the same selections used in over 8 different pivot tables.
    I would be happy to either link these:
    1. by making the selections in a pivot table (by selecting check boxes) or
    2. by having a list of choices somewhere else in the workbook that all the pivot tables linked to.
    Of course, if there is a better way I am open to it too.
    Can anyone help me with this?
    Note: I have below average VBA skills.
    Thanks,

  108. I had tried that previously, and the sample file didn’t seem to work. I am using an older version of Excel (without slicers). It looks like the version check routine didn’t work properly.
    I set “bUseSlicers = False” and it works now.

  109. Jeff,
    Any reason you are using the Worksheet_PivotTableUpdate(ByVal Target As PivotTable) instead of the ThisWorkbook Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) method instead? One place to put the code instead of duplicating it on multiple sheets..

    1. Probably not! I’m in the process of recoding this, so I’ll double-check if there’s any reason why I don’t take your sensible approach for the next version, which promises to be even faster.

  110. The problem I identified wasn’t with the version check routine but with how I’d implemented the bUseSlicers bit. I’m going to take any reference to bUseSlicers out.

  111. Hi,
    This is a very useful code. Just one thing. One limitation is that it works for the variable in the “Report Filter” of the pivot table.
    I have multiple tables where I am trying to replicate the filter applied to the variables in row label section. I tried this in the sample excel file. It did not work.
    Is there a macro with you for this purpose?
    In the Sample file attached, the changes take place when I change the “Item” or “Region” filters and see the change in other pivot tables too … but when I try to change the “Row Labels” it does the reflect in the other pivot tables.
    Hope I am able to explain my problem.

  112. This code is really working for me. It is updating the filter in pivots coming from three different databases. I am also using slicer to modify three different pivot fields (i.e. plant name, Volume type, National/International). The slicers work fine, updating all the pivots, but one. The slicer for “National/International) Pivot field is not updating all of the pivots, just about 4 out of 6. Is there something in the code or is it a limitation?

  113. I used your macro on my file and it worked. thank you. I have 80 or so report filters that I want to expand with the “Show Report Filter Pages.” This seems to only work with one pivot table. In a nutshell, I want to create a macro that “Shows Report Filter Pages” for two pivot tables instead of just one pivot table. Any ideas?

  114. I’m not sure who to give credit to still – Debra, Jeff (I work with another Jeff Weir, strangely enough), et al, but this worked perfectly and I didn’t even have to modify the code! One of the best tools so far, and that I will use often.
    Rob

  115. Hi all,
    I’m on xl2013 – I copied and pasted and it worked perfecto. Then I “refreshed all” and it gave a warning asking a question which of course I ignored, when I checked, all the pivots and all the filters were completed empty!?! (i.e. they show “All”) Now when I change a filter the code doesn’t work 🙁 what have I done?!

  116. I like this code a lot. I have about 10 pivot tables on 4 different worksheets. It is only updating one of them and it is switching the rest to “ALL”. How can I make it so they all change to the filter i.e. Year: 2013 instead of switching to “ALL”?

  117. Excel 2013. Having same issue with this code updating the one pivot table (on current sheet) and setting the rest to “ALL”. Note: I am using PowerPivot with a Data Model from 11 different tables. Three of those tables are large and linked from a SQL database. Not sure which of these things is causing the code to not work; PowerPivot with 11 table data model or link to SQL database. Would love any help. Happy Holidays!

  118. Hi – working with an excel worksheet from last year that has many pivots all of which have different filters, fields, subtotals (e.g., some with sums, counts, average, etc.). I’m looking for a way to swap out 1 field easily from the 100s of pivots that I have setup without having to do each pivot 1 by 1.
    For example the column in last year’s datasource is named “2013 Salary” and I want my pivots to now use the “2014 Salary” column but maintain the same functionality in all pivots.
    Is there a way to easily do this? If I simply rename the column in the datasource and refresh, the 2013 field will disappear altogether from all of my pivtos.
    Thanks!

  119. This has been a fabulous help to me! Thanks so much for sharing this code, and the video. I know that this was done a while back, but sometimes it’s nice to know that work you did a while back is still helping people out today!

  120. I have multiple pivot table on same sheet, and filtering with two criteria, Date and Department, the above code changing both Date and Department, is there is any way you can change the code, which only auto change “Date”.
    Thanks

  121. Fantastic! this code helps me a lot but only have one issue, I have six pivot tables on one sheet with three filers, There is any way I can only add above code to change only single filter. right now if I use above code its change all the filers. Thanks

  122. Thanks a lot for this code! It took me forever to find a way to filter several pivot tables with different caches at once. I also wanted to have a slicer in a different sheet to filter those pivot tables and that did not work until I’d deleted the line ” .ClearAllFilters. ” . I have no idea how that changes things, however it works !

  123. I was thrilled when I first found this, but it seems the code doesn’t work for me.
    I basicly have two pivot tables from different sources (using powerpivot). Does this not work if the pivot table data comes from OLAP/powerpivot ?

  124. Hi there,
    I am attempting to implement this code but I’m running into issues.
    I have 6 pivot tables on the same spreadsheet. 4 are from one data source and 2 are from another.
    They have a field in common that I would like to leverage as a filter but I’m struggling to do this using the VBA code above.
    Unfortunately, there is no good way to re-stage the data to make it happen.
    Any tips on how to use this code with pivot tables from 2 different data sources?
    Thank you!!!
    John

  125. I’m so happy for you guys posting this code, as this is something that I have been working on for days now, and I finally got your code to work for me – except in one case.
    I have two different data sets:
    1. All the available item numbers of items in stock
    2. All the item numbers that actually have been used
    Data set 1 has more item numbers than data set 2. If I filter on some specific items and use your method of applying multiple filter selections, I get more items than I want. I understand why it is happening (because all the filters are first cleared and only the ones found are being reapplied, as there are more items in set 1, I get all those extras as well) however, is there a different method that would exclude all and then only include those that are visible?

    1. @JP
      I was having similar issues. I am using slicers to do the filtering and I am finding that if I create the slicer from the pivot table that has the data set with the most numbers, it works properly.

  126. Hi. Thanks for this code. Works great! I am not very familiar with VBA so this post was very helpful. I was wondering on thing. Is there a way to make this work if I password protect the sheet? When I use the code now and password protect the sheet, all the filters on the second pivot table go to (ALL). Let me know! Thanks!

  127. THIS IS AMAZING, thank you so much… This is going to save so much time. Just one small bug(I know its cheeky to ask).
    Can this code be modified for pivot tables that have two data sets under the ‘report filer’. In my example, I have ‘WEEK’ and ‘PRODUCT’ as ‘Report Filters’. The code works perfect for changing the week, all pivot tables update within the workbook, but if I want to change the ‘Product’ of one Pivot table, all Pivot tables then change. I only want this code to update the week number, not the product?
    Again, I know I should be grateful with the information, but any additional support would be amazing 🙂

  128. Hi Debra
    Firstly, thank you very much for this helpful code. It solved a problem for me perfectly.
    I re-wrote the code from scratch to better understand it, and along the way I made a few changes. The alternative version is below, and hopefully it might be helpful for someone.
    Main changes:
    * Tweaked to enable calling with both a Worksheet and a PivotTable, which means you can hook in to the Workbook_SheetPivotTableUpdate event rather than having to add code to every worksheet you create.
    * Split the code into separate Subs, which should make it easier if anyone wants to affect just a subset of pivot tables, or even a subset of pivot fields.
    * Removed On Error Resume Next and replaced it with slightly more robust error checking. This solved some problems I was having with the original version.
    * Tweaked the code to affect only PageFields on both the “source” and “destination” pivot tables. Not sure if this was the original intention?
    * Changed the nesting of the loops: in my version it’s worksheet/pivot table/pivot field
    * Removed pf.ManualUpdate = True/False: this resolved a problem with losing chart formatting, and removing the lines didn’t seem to cause any problems.
    This works well for me in Excel 2007, but I haven’t tested it in other versions. I hope it’s useful to someone.
    Code to call routine – placed in ThisWorkbook module

    Option Explicit
    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, _
    ByVal Target As PivotTable)
    UpdatePivotTables Sh, Target
    End Sub

    Main routine – placed in a separate module, or in the ThisWorkbook module

    Option Explicit
    Public Sub UpdatePivotTables(ByVal SourceWorksheet As Worksheet, _
       ByVal SourcePivotTable As PivotTable)
       ' Updates page field filter criteria
       '  on all pivot tables in the current workbook
       ' to match those chosen in
       '  SourcePivotTable on SourceWorksheet
       On Error GoTo ExitSub
       Dim ThisWorksheet As Worksheet
       Dim ThisPivotTable As PivotTable
       Application.ScreenUpdating = False
       Application.EnableEvents = False
       ' Otherwise we'll get a never-ending cascade of pivot table updates!
       For Each ThisWorksheet In ThisWorkbook.Worksheets
           If ThisWorksheet.Type = xlWorksheet Then
           ' Don't try to process Chart sheets, etc.
               For Each ThisPivotTable In ThisWorksheet.PivotTables
                   If (ThisWorksheet.Name <>  SourceWorksheet.Name) _
                   Or (ThisPivotTable.Name <>  SourcePivotTable.Name) Then
                   ' Skip the source pivot table
                       SyncPivotTable SourcePivotTable, ThisPivotTable
                   End If
               Next ThisPivotTable
           End If
       Next ThisWorksheet
    ExitSub:
       ' These lines should always be executed,
       'to ensure Excel is left in a usable state
       Application.EnableEvents = True
       Application.ScreenUpdating = True
    End Sub
    Public Function SyncPivotTable(SourcePivotTable As PivotTable, _
          DestinationPivotTable As PivotTable)
     ' Synchronises page field filters between
     '  SourcePivotTable and DestinationPivotTable
    Dim SourcePivotField As PivotField
    For Each SourcePivotField In SourcePivotTable.PageFields
        If PivotFieldExists(DestinationPivotTable.PageFields, _
            SourcePivotField.Name) Then
            SyncPivotField SourcePivotField, _
               DestinationPivotTable.PageFields(SourcePivotField.Name)
        End If
    Next SourcePivotField
    End Function
    Public Function SyncPivotField(SourcePivotField As PivotField, _
       DestinationPivotField As PivotField)
        ' Synchronises filter items between
        '  SourcePivotField and DestinationPivotField
    Dim SourcePivotItem As PivotItem
    With DestinationPivotField
        .ClearAllFilters
        If SourcePivotField.EnableMultiplePageItems Then
            .EnableMultiplePageItems = True
            .CurrentPage = "(All)"
            For Each SourcePivotItem In SourcePivotField.PivotItems
                If PivotItemExists(.PivotItems, SourcePivotItem.Name) Then _
                  .PivotItems(SourcePivotItem.Name).Visible = _
                      SourcePivotItem.Visible
            Next SourcePivotItem
        Else
            .EnableMultiplePageItems = False
            .CurrentPage = SourcePivotField.CurrentPage.Value
        End If
    End With
    End Function
    Private Function PivotFieldExists(MyPivotFields As PivotFields, _
          PivotFieldName As String) As Boolean
        ' Returns True if PivotFieldName exists in
        '   MyPivotFields, False otherwise
        Dim Temp As Variant
        On Error Resume Next
        Temp = MyPivotFields(PivotFieldName)
        PivotFieldExists = (Err = 0)
    End Function
    Private Function PivotItemExists(MyPivotItems As PivotItems, _
       PivotItemName As String) As Boolean
        ' Returns True if PivotItemName exists in
        '  MyPivotItems, False otherwise
        Dim Temp As Variant
        On Error Resume Next
        Temp = MyPivotItems(PivotItemName)
        PivotItemExists = (Err = 0)
    End Function
    
      1. You’re welcome Debra.
        I noticed an error has crept into the code above. The following line:
        If (ThisWorksheet.Name = SourceWorksheet.Name) Or (ThisPivotTable.Name = SourcePivotTable.Name) Then
        should read:
        If (ThisWorksheet.Name SourceWorksheet.Name) Or (ThisPivotTable.Name SourcePivotTable.Name) Then
        In other words, the “equals” should be “not equals”.

      2. And now I see how the error crept in! The less than/greater than signs are being stripped out! Let me try again:
        If (ThisWorksheet.Name <> SourceWorksheet.Name) Or (ThisPivotTable.Name <> SourcePivotTable.Name) Then

  129. Hi Debra,
    Thanks for this code – it has been extremely useful!
    Can I go tweak this to not treat ptmain as the Target but instead a fixed Pivot Table?
    The macro I am currently using updates my 8 pivot tables’ filters to be equal to whatever value is in a cell on my sheet. I had to define 8 different Pivot Tables and Pivot Fields, and then write out code to change each one individually which is cumbersome and undoubtedly slows my sheet down.
    I want to leverage your macro so that when I change the value in my cell it updates only one of my PivotTables (which would be assigned to ptmain) and then your macro would detect a change to ptmain and kick in, hence updating the remaining 7 pivots.
    Any help on this would be really appreciated!
    Many thanks
    Steve

    1. Steve – one way you can do this fairly easily is by setting up a new PivotTable that masquerades as a Data Validation Dropdown, and putting the fields you want to sync into the Filters pane as per my post at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/. Let’s call it the ‘Master’ pivot. When users make changes to the master, the other pivots get updated. Because the master only has fields in the filters pane, it looks like a series of data validation boxes instead of a traditional PivotTable, but easily triggers Debra’s code.
      If your PivotTables are big – or if you have many of them – then if users are only going to be choosing one item from each dropdown it will be much more efficient if you were to use an amended version of Debra’s code that gets triggered by a Worksheet_Change event than the PivotTable_Update event, because you could feed in the actual PivotField name that a user just changed, and have the code sync just that field across all the PivotTables concerned. Debra’s code above syncs all fields even though just one of them changed, and this can take some time on big PivotTables. That’s because Excel VBA doesn’t give the user much information regarding users’ interaction with PivotTables. You only get access to what’s called a PivotTable_Update event that tells you ‘Hey, this particular PivotTable had some kind of change made to it’ but it doesn’t tell you the nature of the change. And if that change was actually due to a user filtering it, the event doesn’t tell you what particular field was filtered – it only tells you that something changed in regards to PivotTable X. So you’re forced to sync ALL PivotFields any time any kind of change was made to that PivotTable, even if a user didn’t change a PivotField filter of interest. Even if they didn’t change any PivotField filter at all.
      But following my approach above, and using a variation of the more efficient approach outlined at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ – means you can correctly identify the particular PivotField that changed, and then sync the other PivotTables with just that field.
      I also happen to be working on some revised code that will intelligently works out whether the PivotTable_Update event was caused by a user filtering the Pivot, and then my code tries to work out by process of elimination which particular field got changed. My next step is to work on the macro that helps a user set this up on any workbook they want just by clicking a ‘SyncPivot’ icon in the ribbon and then following instructions. Ultimately this is going to be bundled into a commercial add-in, but I need some beta testers so flick me an email on [email protected] if you want to try it out.

  130. I’m quite late to the party on this, but I just wanted to send the biggest and most heart-felt “thank you” that I possibly can over the internet. This code and the subsequent edit for a single worksheet have made portions of my job so much easier and I cannot thank you enough.

  131. Hi Doug
    Thanks for this very enlightening info. I have a question for you, I have tried your code and it works well if you want to change the pivot filters on ALL sheets in the workbook. However I want to change the filters on all pivot tables ONLY in one sheet and leave the pivot tables on the other sheets unaffected. I tried manipulating your code but I made no progress. Do you have any suggestions on how to do this?
    Thanks in advance!

  132. Hi Debra,
    Thanks for the tutorial.
    If ws.Name & “_” & pt wsMain.Name & “_” & ptMain Then
    This particular line, I would like to know why is there a need to compare the names of the main and other worksheets plus the “_” & pt

  133. Hi Debra (All),
    Did anyone ever work out how to resolve the chart formatting problem for Excel 2010, similar to Bretts post on January 9, 2013 at 2:15 pm. I have a primary (clustered column) and secondary axis (line with markers) that defaults the designs and colours back to default.
    I have also tried removing pf.ManualUpdate = True/False from the code with no success.
    Ben.

  134. Thanks Debra Dalgleish for your original code and Alastair Bishop for your reworked code.
    @Alastair Bishop, your code is exactly what I am looking for as it maintains the pivot chart formatting, however it has some peculiar results.
    I have a consolidated pivot table (i.e. SourcePivotTable in SourceWorksheet) to determine all available periods (e.g. 201501, 201502, etc.) across 4 worksheets and separate pivot tables for each worksheet where the periods originated from.
    If I select multiple categories (i.e. Periods) in the SourcePivotTable it selects the corresponding periods in the 4 pivot tables, even if that category doesn’t exist (perfect). However, in some instances if I select an individual category, it selects the correct category in some of the pivots and not in others (weird).
    I would appreciate any assistance if possible!
    Ben.

  135. This code works well but Excel crashes after clicking on Refresh Pivot Table?
    Did anyone else had this problem?
    Could you help please ?

  136. I have only one sheet, multiple Pivot Tables with multiple filters, this code works good only when i choose one item.
    Could you please tell which correction should i do to make this code work one multiple item selection too ?
    I will really appreciate your help
    Imane From France.

  137. Hi Debra,
    I am great admire of yours! May I ask u a quick question. I need to link one main slicer to many other slicer from different tables and different sources. So the idea is to click on a topic on the main slicer, which topic or label is present on the other slicer, then other slicer are activated as well.
    Thank you for your help a lot.
    Best,
    Arben

  138. Hi All
    Thought I’d contribute with a version based on the original concept, but using the Workbook event
    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    ‘Synchronise all Workbook PivotTable PageFields to Source PivotTable
    Dim wsSource As Worksheet
    Dim ptSource As PivotTable
    Dim pfSource As PivotField
    Dim piSource As PivotItem
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set wsSource = Sh
    Set ptSource = Target
    For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
    If (ws.Name wsSource.Name) Or (pt.Name ptSource.Name) Then
    For Each pfSource In ptSource.PageFields
    Set pf = pt.PivotFields(pfSource.Name)
    pf.EnableMultiplePageItems = pfSource.EnableMultiplePageItems
    Select Case pf.EnableMultiplePageItems
    Case False
    pf.CurrentPage = pfSource.CurrentPage.Value
    Case True
    For Each piSource In pfSource.PivotItems
    pf.PivotItems(piSource.Name).Visible = piSource.Visible
    Next piSource
    End Select
    Set pf = Nothing
    Next pfSource
    End If
    Next pt
    Next ws
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

  139. Debra,
    This is an amazing tool! I’m a complete novice with this, but was able to use your script to effectively tie different sources together…. buuuuuut, I was wondering if there is a way to make this work with PivotTables built off of a PowerPivot. Basically, I have a data set in my file, and then three other pivot tables connecting to different PowerPivots saved on a SharePoint website. These data sets are HUGE, so I have to connect to them through data connections on SharePoint. Is there a way to get this script to affect both the pivot tables connected to the data set saved in my file, and the pivot tables connected to the outside data source?

  140. Great script!
    I used to to easily allow me to easily create 40 versions of the same file with a different “MDA” selected across about 50 sheets for each version. I then use another script I pieced together from other helpful people to create a PDF and email it to the chosen “MDA.” The problem is that (1) each of the 40 files needs to refresh the same data from the SQL server and (2) any changes I make to the file need to be made 40 times. I would like to change your script to change all the tables in the document with the “MDA” field to one name. I will then insert it into the script below, putting it below the refresh statements and above the create/email PDF statements. Then I will copy that portion 40 times. Issues: Your script goes in the sheets, but the other script is a module. How do I tell your script the change all the “MDA” fields to my selected value. I have included the version of your script I am currently using and the create/email PDF script below.
    **** Here is the module I want to insert your script into ***********
    Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
    Dim FileName As String
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    ActiveSheet.DisplayPageBreaks = False
    ActiveWorkbook.RefreshAll
    DoEvents
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    ActiveSheet.DisplayPageBreaks = True
    Application.EnableEvents = True
    ‘ ******thought I would put version of your script here*******
    FileName = RDB_Create_PDF(Source:=Range(“A1:n44″), _
    FixedFilePathName:=”S:\AnesthesiaQA\Dashboards\Publication\B.pdf”, _
    OverwriteIfFileExist:=True, _
    OpenPDFAfterPublish:=False)
    Set iMsg = CreateObject(“CDO.Message”)
    Set iConf = CreateObject(“CDO.Configuration”)
    iConf.Load -1 ‘ CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) _
    = “owa.hospitalXXX.edu”
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
    .Update
    End With
    strbody = “Anesthesiologists and CRNAs,” & vbNewLine & vbNewLine & _
    “Attached is the Monthly QA Dashboard” & vbNewLine & _
    “There is a department version and one specific to your patients. Please see me if you have any questions,” & vbNewLine & _
    “DoctorB” & vbNewLine & _
    “”
    With iMsg
    Set .Configuration = iConf
    .To = “[email protected]
    .CC = “”
    .BCC = “”
    .From = “””Dr. B”” ”
    .Subject = “Monthly Anesthesia Dashboard”
    .TextBody = strbody
    .AddAttachment “S:\AnesthesiaQA\Dashboards\Publication\B.pdf”
    .AddAttachment “S:\AnesthesiaQA\Dashboards\Publication\Dashboard.pdf”
    .Send
    End With
    Kill (“S:\AnesthesiaQA\Dashboards\Publication\B.pdf”)
    ‘Kill (“S:\AnesthesiaQA\Dashboards\Publication\Dashboard.pdf”)
    ‘ **** This is where I would copy and paste the 40 times your script followed by email/createPDF for each “MDA”*************
    ActiveWorkbook.Close savechanges:=True
    Application.Quit
    End Sub
    ******** Here is the version of your script I currently have in my document on sheet1 and on ThisWorkbook **********
    Option Explicit
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim wsMain As Worksheet
    Dim ws As Worksheet
    Dim ptMain As PivotTable
    Dim pt As PivotTable
    Dim pfMain As PivotField
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim bMI As Boolean
    On Error Resume Next
    Set wsMain = ActiveSheet
    Set ptMain = Target
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
    If ws.Name & “_” & pt wsMain.Name & “_” & ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(pfMain.Name)
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = “(All)”
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt
    Next ws
    Next pfMain
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

  141. Debra,
    The formula works great however it only allows me to use two slicers that will accurately filter both tables. Once I click the other two slicers only one of the tables gets filtered. Is there any alteration to the code that would allow me to use more slicers?

  142. I have really been struggling with a solution for this and am beginning to believe it really is impossible.
    I have two pivot tables from two different data sources that cannot be combined because of the type of data (one source it detailed while the other is a high view of totals), however there is one column in both ranges which are identical “Departments”.
    What I would like to do is filter “Departments” on Pivot1 and have it filter to the same department selection on Pivot2. Is it possible to manipulate this coding to detect and include a pivot table from a different cache?
    Very grateful for any help with this.

  143. Hello guys,
    Thank you so much for all your help this code has practically solve my whole life and problems that I had. By any chance would any one know of a way to be able to display on a separate cell in the same worksheet, the items that were selected from the list. in my case I have a filter named Dates which I select multiple dates from it what I want is to be able to display the earliest and the latest date selected from the list. Any help will be gladly appreciated

  144. Hello, and many thanks for the help on the code.
    However I need your help on the below:
    I have a master filter were I do my choices, then I have a sheet with several pivot that will automaticaly update when the master filter is selected. On the 3rd sheet, I also have several pivot, but on this sheet I need the general information, so I need to “exclude the code” for this sheets
    How can I do it?
    Thanks in advanced

    1. Frederico, you can add a couple of lines to check the sheet name:

      For Each ws In ThisWorkbook.Worksheets
           If ws.Name <> "MySheetName" Then '==new line 1
              For Each pt In ws.PivotTables
                  'all the existing code here
              Next pt
          End If   '==new line 2
        Next ws
  145. Hi all
    Anyone having success using this brilliant functionality having Data Cubes as the source for the pivot tables ?
    Best Regards
    Lasse

    1. Lasse: Can’t you just connect a slicer up to each of the PivotTables concerned? Or are they based on completely different data sources?

  146. Hi,
    This is a great bit of code. I was wondering if there is any enhancement to this whereby a pivot table returns a blank if the selection doesn’t appear ? I am selecting an individual person in my main pivot that exists in a number of other pivots, except one. This pivot returns all the data and I wanted it to return nothing (blank). Is this possible?
    Thanks,
    Chris

  147. Hi,
    Is there an easy way to update slicers on the subsequent tabs based on the selections from the first tab? I have duplicate slicers on each tab, which do not get updated as the page field filters are updated. I have numerous worksheets, all with pivots, but I want my users to be able to use the slicer on each tab (all different data sources, but with all common slicers).
    Thanks
    Bill

  148. Hi All,
    What an amazing code, thanks you!
    I’m a VBA rookie, wanted to know what should I do in order to apply this code only on a certain field?
    In Debra’s example, i want to make all the pivot tables to change according to the employee field (that won’t happen now and I’m wondering why)

  149. Hi Debra,
    Thank you for the great codes and lessons. They have saved me in several instances.
    I am trying to filter multiple items in 2 pivot tables in different tabs using the macro in ,”PT0025 – Change All Page Fields with Multiple Selection Settings”. The filter names are same for both pivot tables which the data sources are different. There are common items among multiple items to select in the drop down as well as different items ( though the filter name is same).
    When I apply multiple filters to 1st pivot table , the 2nd pivot table picks selected items in pivot table 1 filter and some other non-listed items. At times the 2nd tables picks entirely different items, if the selected item in pivot 1 is not available in pivot 2.
    Your help is highly appreciated.

  150. Hi
    I have 5 different sets of data on 5 different sheets which have two key fields that are common across all data sets in all the sheets
    I used pivot tables in all the sheets and filter on the common field in each sheet. The data is the used to generate graphs and the scales for the graphs are calculated using Private Sub Worksheet_Calculate().
    Earlier I used to filter each pivot table manually in each sheet. But with your code I am able to select an item in the pivot table in the active sheet and thee same item gets selected in the pivot table in all other sheets as well.
    However, the graphs which are generated from the pivoted data get calibrated with the Private Sub Worksheet_Calculate() only in the active sheet. Only upon refreshing the pivot table data in any of the sheets do the graphs in the other sheets get re-calibrated.
    What should be done so that I do not have to refresh the pivot table one more time?
    I have tried ActiveWorkbook.RefreshAll which takes too long….
    Pls suggest some solution.
    Thanks

  151. Hi, first thank you for such a beautiful code. Works like charm.
    One issue I am facing. I have taken a Slicer based on the one of the Pivots. So, when I use the slicer, after everything gets updated, the sheets report filter gets selected by default.
    I have tried putting the code to activate the 1st cell of the sheet but still the report filter cell gets selected. How to work around this?

  152. Hi Debra,
    this is so sinsightful,
    is there any chance to use more than one slicer not only market but also other variables to be applied with your code?

    regards,
    Fariz

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

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