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.

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.

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.

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.

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.

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!

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

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?

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!

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.

Hi Tim, pivot chart formatting doesn’t stick well in some versions of Excel. If you’re asking for help in a forum, be sure to mention which version you’re using.
The formatting issue is mentioned in a couple of MSKB articles, and you can find one in my Pivot Table FAQs: http://www.contextures.com/xlfaqPivot.html#ChartFormat

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!

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

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.

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!

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

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!

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.

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?

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.

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

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

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?

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.

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.

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?

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?

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

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…

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.

Hi Alex,
I’ve put a sample file on my Contextures website that might help you. In the file, when you change a Month, the applicable quarter shows. If you select a quarter, the Months field changes to “All”.

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.

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

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

@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

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.

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?

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

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

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.

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.

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

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.

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.

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

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?

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

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.

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.

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:

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?

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?

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

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.

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.

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

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

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.

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

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

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.

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?

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

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?

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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!

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

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!

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

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

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.

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.

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?

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.

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?

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.

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.

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.

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.

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

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

‘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;”

‘———————————————————————————–
‘ 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

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

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

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

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

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.

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.

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?

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

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.

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.

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.

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

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.

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?

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?

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.

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

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

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!

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.

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!

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

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

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 !

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?

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!

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

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.

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.

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.

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 weir.jeff@gmail.com if you want to try it out.

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.

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?

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.

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

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

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?

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.

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?

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?

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

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

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?

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

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)

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.

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?

[…] 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 […]