Change All Pivot Tables With One Selection

Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.

pirates

Change All Pivot Table Filters

Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.

For example, 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.

pivotmultichange01

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, and changes it on all the pivot tables with the same report filter field. The code loops through all the worksheets in the file, and 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 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 Excel 2010, if you enable macros.

Watch the Excel Video Tutorial

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

Or watch on YouTube: Change All Pivot Tables When One Changes

______________________

208 comments to Change All Pivot Tables With One Selection

  • K. NARAYAN

    Hi ,

    What is the reason you have two "On Error Resume Next" statements ?

    Narayan

  • Sachin

    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!

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

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

  • David Tigue

    That looks like one hell of a party!!

  • Iassen

    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

    • Becca

      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

  • Raks

    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?

  • Jenny

    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

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

    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

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

        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!

  • Becca

    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

  • Jenny

    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

  • Timbo

    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

  • John

    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!

  • Umesh kokitkar

    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

  • Richard

    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,

    • Ryan

      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!

  • SKS

    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

    • HVT

      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!

  • Dylan

    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.

  • Amit

    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

  • May Lanie

    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

  • Andres

    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

  • Edoardo

    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

  • Kevin

    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?

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

  • Fei

    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

  • Hoo

    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?

  • Graeme

    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.

  • Phil

    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!

  • Jeff Weir

    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.

    • Phil

      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.

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

  • Ryan

    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?

  • Jeff Weir

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

  • DeXTeR

    Debra,

    This was really helpful..

    Thanks,
    DeXTeR

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

  • Alex

    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.

  • Tony

    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.

  • Jeff Weir

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

    • Tony

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

  • Jeff Weir

    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

  • Jeff Weir

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

    • Tony

      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.

  • Ryan

    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?

  • Jeff Weir

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

    • Ryan

      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

  • Jeff Weir

    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.

  • @Jeff, thanks, and this code is probably 8-10 years old, so it's time for an update.

  • Darren Chapman

    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

  • Jeff Weir

    @Darren...yep, this code should do it. Even better, head on over to http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/ and you'll find an updated approach I wrote that Debra posted. Note that I've since found an even faster approach that I'm still in the process of punching up, and will submit to Debra in due course.

  • Darren Chapman

    @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

  • Darren Chapman

    @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

  • Jeff Weir

    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.

  • Darren Chapman

    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

  • Jeff Weir

    @Darren - can you ask this question over at the other post where the code is, and i'll answer it there. Cheers.

  • Sarah

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

  • Richard Lowery

    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.

  • Loshini

    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

  • Hannes

    Hi Debra! Thanks for your work! You saved my life :)

  • Alan

    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?

  • Alan

    I'm running Excel 2010 on Windows 7 64bit.

  • Benjamin

    Hi Debra,

    Any Luck with the OLAP query?

    Thanks!

  • Chris

    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

  • Brett

    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

    • Brett

      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?

  • J

    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?

  • Jay

    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

  • Ian

    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

  • VKS

    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

  • Denet

    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?

  • Denet

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

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

  • Denet

    Jeff,

    Ok. That's what I was thinking after looking around on the internet. Thank you for the response.

  • Jay

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

    • Drew

      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.

      • Jeff Weir

        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.

  • Paul

    I love you! Your code is great!

  • Zach Le

    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?

  • Lance

    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

  • Jeff Weir

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

  • Jeff Weir

    @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

  • David

    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

  • Jeff Weir

    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?

  • Billy Hawkins

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

  • Jeff Weir

    @Billy: Try the updated version of the code at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/ and see if that fixes the issue.

    If not, post back here and I'll take a look.

  • Cindi

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

    • Cindi

      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.

      • Cindi - I suggest you try both the revised code mentioned in the comments above and also try slicers as per above comments

        • Cindi

          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.

          • Jeff Weir

            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?

  • Billy

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

  • Raghu

    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?

  • Jeff Weir

    Raghu - Try the updated version of the code at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/

    By the way, what version of Excel are you using? I have some even faster code for Excel 2010 or later

  • Changing Multiple Pivot Tables At Once | A Digital Marketing Blog

    [...] the one I found most helpful in this case was this post. Unfortunately I tried adding the code on this page and pasting into my work book but I [...]

  • Raghu

    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

  • Jeff Weir

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

  • Jeff Weir

    @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

  • Ian B

    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.

  • Jeff Weir

    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?

  • Ian B

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

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

  • Hi again Ian. I'm curious what this dashboard is actually for? Sounds interesting!

    Regards
    Jeff

  • Ian B

    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

  • Ian B

    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.

    • Steve

      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

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

  • Great! How long does it take to sync all the pivots?

  • Jeff Weir

    @IAN - Try the new code at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ which should be even faster.
    @All - suggest you also update the code with this latest version. Note that the workbook contains two code modules, and you need BOTH of them.

  • shannon

    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!

  • I think so. Don't see why not. Give it a try, and post back if you have any issues.

    • shannon

      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

  • maritrack

    Hi, How do i update the code to make 1 specific pivot as a master pivot? thanks!

  • So you only want the code to sync tables if a particular pivottable is changed?

  • maritrack

    yes, this is exactly what I want to do

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

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

  • maritrack

    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

  • [...] Can you please go through the link. Change All Pivot Tables With One Selection | Contextures Blog Thanks, [...]

  • [...] You will need code because you will have to attach the slicer to a dummy pivot table and then use that table's update event to synchronise the filters of the other tables - see for example Debra's page here: Change All Pivot Tables With One Selection | Contextures Blog [...]

  • Le'Nae

    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?

  • Le'Nae

    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.

  • JHN

    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

  • JHN - why not check if an item in one slicer exists in the other, and if id doesn't exist then don't hide it.

  • JHN

    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

  • How many items are in the 'slave' pivot and the 'master' pivot? Are we talking tens, hundreds, thousands, or many thousands?

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

  • JHN

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

  • shannon

    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

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

  • MABulator

    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?

  • shannon

    Ok. Thank you jeff.

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

  • Le'Nae

    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.

  • JHN

    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.

  • Bill

    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.

  • Bill, if you have slicers, why do you need this code? Slicers can control multiple pivots.

  • Alex

    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?

  • gia

    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.

  • SD

    Hi there is there a way to apply this to all tables (autofilter) instead of pivot tables?

    Thanks a ton for any help :)

    SD

  • Torbjorn

    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

  • 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 weir.jeff@gmail.com so I have your return email address, and I'll send the amended code to you when I'm finished redeveloping it.

  • Steve Brotz

    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

  • Lars

    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 weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz
    '
    ' 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

    • Lars Johnson

      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

  • Kajaj

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

  • kajaj - if you are using Excel 2010 or later, use Slicers to accomplish this, as this functionality is built-in and much faster. If using an earlier version, then use my code posted at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/

    • Kajaj

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

  • Kajaj: What version of Excel do you have?

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

    • Kajaj

      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.

  • Rob Lovejoy

    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?

  • Rob - what version of Excel are you using?

  • Rob Lovejoy

    Jeff, I am using 2007.

  • Rob Lovejoy

    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.

  • 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

  • Rob Lovejoy

    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!

  • 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 weir.jeff@gmail.com if you can't see this, or if the code doesn't play well with Excel 2007, and i'll help you out.

  • Marina

    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.

  • And here's a prompt response: try the code at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ because it's substantially faster, and it handles any type of field. Post back if you get into difficulty at that thread.

    • Asad

      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.

      • Jeff Weir

        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.

  • Alexander Cook

    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.

  • Mike Kroupa

    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,

  • Jeff Weir

    Mike: take a look at my routine at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ which should let you do what you need.

  • Mike Kroupa

    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.

  • Steve

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

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

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

  • […] werden muss. Debra Dalgleishhat dafür eine Lösung entwickelt die ich empfehlen […]

  • Harsh Srivastava

    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.

  • Jeff Weir

    Harsh - if you are using Excel 2010 or later, use Slicers to accomplish this, as this functionality is built-in and much faster. If using an earlier version, then use my code posted at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/

  • Karla

    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?

  • Rourke Boyakin

    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?

  • Robin Rascon

    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

  • Well, it dit work but then...

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

  • BAudet

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

Leave a Reply

  

  

  

You can use these HTML tags

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