There is a new sample file on the Contextures website, with a macro to change all pivot tables with one selection, when you change a report filter in one pivot table.
Change All Pivot Tables
In the sample workbook, if you change the “Item” Report Filter in one pivot table, all the other pivot tables with an “Item” filter will change.
They get the same report filter settings that were in the pivot table that you changed.
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, to change all Pivot Tables with the same report filter field.
The code loops through all the worksheets in the workbook, and loops through each pivot table on each sheet.
Private Sub Worksheet_PivotTableUpdate _ (ByVal Target As PivotTable) Dim wsMain As Worksheet Dim ws As Worksheet Dim ptMain As PivotTable Dim pt As PivotTable Dim pfMain As PivotField Dim pf As PivotField Dim pi As PivotItem Dim bMI As Boolean On Error Resume Next Set wsMain = ActiveSheet Set ptMain = Target Application.EnableEvents = False Application.ScreenUpdating = False For Each pfMain In ptMain.PageFields bMI = pfMain.EnableMultiplePageItems For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables If ws.Name & "_" & pt <> _ wsMain.Name & "_" & ptMain Then pt.ManualUpdate = True Set pf = pt.PivotFields(pfMain.Name) bMI = pfMain.EnableMultiplePageItems With pf .ClearAllFilters Select Case bMI Case False .CurrentPage _ = pfMain.CurrentPage.Value Case True .CurrentPage = "(All)" For Each pi In pfMain.PivotItems .PivotItems(pi.Name).Visible _ = pi.Visible Next pi .EnableMultiplePageItems = bMI End Select End With bMI = False Set pf = Nothing pt.ManualUpdate = False End If Next pt Next ws Next pfMain Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Download the Sample File
To test the Change All Pivot Tables code, you can download the sample file from the Contextures website.
On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 – Change All Page Fields with Multiple Selection Settings.
The file will work in Excel 2007 or later, if you enable macros.
Watch the Video
To see the steps for copying the code into your worksheet, and an explanation of how the code works, watch this short video.
______________________
Hi ,
What is the reason you have two “On Error Resume Next” statements ?
Narayan
@Narayan, thanks, I hadn’t noticed that, and I’ve removed one of the “On Error Resume Next” statements.
Hi ,
Thanks.
Narayan
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.
Hi Debra,
Dim ptMain As PivotTables
I see ptMain is defined as (PivotTables). So, can I assign multiple Pivot tables to the variable ptMain? If so, how will I do it?
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!
Thanks Doug! It’s an interesting little twist for the newer versions of Excel.
That looks like one hell of a party!!
@David, I know! It was crazy. 😉 Fortunately, it was over pretty early.
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
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
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?
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
for some reason my code is not working. I am just trying to change one specific field on a worksheet for all pivot tables.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
Set pfMain = ptMain.PivotFields(“open date”)
bMI = pfMain.EnableMultiplePageItems
For Each pt In wsMain.PivotTables
If pt ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(“open”)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = “(All)”
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False
Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
i removed the Next ws and next pfMain, but didnt work either
nvm i found the answer! ty
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:
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!
Thanks Liz! I’m glad you found this article, and have fun experimenting with VBA.
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
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
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
Hi Debra, just letting you know that I’ve also posted the question on Mr Excel. Suspect it is not code related per se – perhaps a more general Excel issue that someone may be able to shed some light on.
Cheers
Tim
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
Thanks Debra,
I’ll check out those resources.
Cheers
Tim
Resurrecting an old thread here, but I had the same issue and found a solution that works for me (Excel 2007).
Like you I lost chart formatting when running the macro, but it worked fine if I stepped through it!
Removing the
pt.ManualUpdate = True/False
lines solved the problem for me, and didn’t appear to cause any issues. Hope this is helpful to someone.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!
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
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,
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!
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.
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
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
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
@Andreas, you could set up a cell where you enter yes/no, and refer to that cell at the start of the code. If it’s yes, then run the filter code, and if it’s no, exit the macro.
That’s exactly what I ended up doing! Great piece of code though, very useful.
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
@Edoardo, there is a new sample file that lets you select the fields that you want to update.
The file is on the Contextures website, on the Sample Excel files page:
http://www.contextures.com/excelfiles.html#Pivot
Look for PT0028 – Change Specific Page Fields with Multiple Selection Settings
Hi Debra,
Thank you for the great codes and lessons. They have saved me in several instances. You are really great.
I am trying to filter multiple items in 2 pivot tables in different tabs using the macro in ,”PT0025 – Change All Page Fields with Multiple Selection Settings”. The filter names are same for both pivot tables which the data sources are different. There are common items amoung multiple items to select in the drop down as well as different items ( though the filter name is same).
When I apply multiple filters to one pivot table , the other tables picks listed items and some other non-listed items from the list of items in the 2nd pivot table. At times the 2nd tables picks entirely differengt items, if the selected item in pivot 1 is not available in pivot 2.
Could you please have a look at this issue.
Many Thanks ,
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
@Fei, you could try adding this line after the Set wsMain line:
Set wsMain = ActiveSheet
If wsMain.Name <> Me.Name Then GoTo exitHandler
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?
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.
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!
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.
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?
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?
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…
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.
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”.
Report Filter VBA
Thanks so much Debra!
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.
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
@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?
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.
No problem…glad you got it sorted. That warning bar is very easy to miss.
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.
Hello all,
This code and blog are great and very helpful.
I have the exact same problem as both guys above. I too would really appreciate it if there could be a solution to this problem.
Thanks for sharing your knowledge
John…what’s the problem you have? THe last two posts above are for different issues. Is it speed? If so, Try the updated version of the code at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/
And if you have excel 2010 or later you can use slicers, which are even faster.
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.
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
@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.
@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
@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
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.
Cheers Jeff for all your help
@Darren – can you ask this question over at the other post where the code is, and i’ll answer it there. Cheers.
THANK you for this. I was able to adapt your code very quickly to serve my needs exactly. I really appreciate your help!!!!!!
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.
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
Hi Debra! Thanks for your work! You saved my life 🙂
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?
I’m running Excel 2010 on Windows 7 64bit.
Hi Debra,
Any Luck with the OLAP query?
Thanks!
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
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
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?
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
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
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
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?
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.
Jeff,
Ok. That’s what I was thinking after looking around on the internet. Thank you for the response.
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)?
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.
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.
I love you! Your code is great!
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?
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
@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 – thank you, that worked!
@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
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
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?
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!!!! 🙂
@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.
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!!
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
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?
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!!!
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?
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
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
@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.
Give this a try, and if you have any issues post back here.
Cheers
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.
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?
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
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
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.
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?
@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.
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.
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
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?
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.
Thanks so much! I’ll give it a shot
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).
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.
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
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?
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.
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.
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.
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).
JHN: Because Slicers generate a Pivot Update event, you should be able to use my code posted at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ to sync the other pivot, which will also effectively sync the other slicer.
But I’ve just discovered a bug in that code if there is already a slicer in the workbook. Let me fix that bug, and see if the code then will do what you need it to. Might be a couple of days.
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.
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?
MABulator: Unfortunately the code doesn’t work with data cubes, and I don’t have data cubes to experiment with in order to see if it can be amended.
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
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.
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.
Flick me a copy to [email protected] and I’ll take a look.
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.
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?
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.
Hi there is there a way to apply this to all tables (autofilter) instead of pivot tables?
Thanks a ton for any help 🙂
SD
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 [email protected] 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
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
I was able to modify a good portion of the code in the “SyncPivotsAnyVersion” function to work with OLAP data cubes. You can still specify worksheets and pivot tables to exclude, but it does not have the ability to exclude pivot fields. Also, it only syncs the page fields (which i believe are all the fields that are in the report filter section of the pivot table). I’m fairly new to VBA so take my code below for what it is and know that it’s likely not the best method. I usually don’t post on sites like this but it seemed like there were a lot of people struggling with this.
Option Explicit
Sub SyncPivotsAnyVersion(Target)
‘ Revised by Lars, November 2013 to work with OLAP cubes
‘ Originally Downloadeded from http://www.contextures.com
‘ Revised code by Jeff Weir, June 2013
‘ Contact [email protected] or [email protected]
‘
‘ Description: Select an item in a pivot table’s page fields, and
‘ page fields for other pivot tables in workbook will
‘ change to the same Item.This macro sets whatever is selected in the
‘ In the code, you can specify
‘ * any worksheets to be ignored; and
‘ * any pivot tables to be ignored.
‘
Dim pt_Master As PivotTable
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
‘CPL stands for CurrentPageList
Dim bCPL As Boolean ‘This boolean gets used to tell the loop when to exit
Dim CPL_Numbers_Array() As Long ‘This array lists the number if items in the CurrentPageList for each PivotField
Dim CPL_String As String ‘This string will contain all items in the CurrentPageList
‘and those items will be separated by semicolons
Dim CPL_String_Array As Variant ‘This array contains all items in the CurrentPageList
Dim varExcludePivots As Variant
Dim varExcludeSheets As Variant
Dim varTest As Variant
Dim lng As Long
Dim strExclusions As String
Dim bDoNothing As Boolean
Dim i As Integer
Dim J As Integer
‘Set to false to prevent Excel event handlers from being called
Application.EnableEvents = False
Application.ScreenUpdating = False
Set pt_Master = Target
ReDim Preserve CPL_Numbers_Array(pt_Master.PageFields.Count – 1)
‘specify any WorkSheets that you DON’T want to check
‘in section marked with ‘*******
‘specify any PivotTables that you DON’T want to change (but that are in WorkSheets that you DO want to check)
‘in section marked with ‘=======
‘**********************************************************************************
‘ Here’s where we list any WorkSheets that we want the code to skip
‘ We simply list them below like so (without the apostrophe ‘ at the front):
‘ strExclusions = strExclusions & “PUT WORKSHEET NAME HERE;”
‘ For this particular example, we will tell Excel to only ignore the
‘ worksheets called ‘Region Reports’, ‘Pivot Data’, and ‘MyLinks’
strExclusions = strExclusions & “Notes;”
strExclusions = strExclusions & “Summary;”
‘**********************************************************************************
If strExclusions = “” Then strExclusions = “;”
varExcludeSheets = Split(strExclusions, “;”)
strExclusions = “”
‘———————————————————————————–
‘ Here’s where we list any PivotTables that we want the code to skip.
‘ We simply list them below like so (without the apostrophe ‘ at the front):
‘ strExclusions = strExclusions & “WORKSHEET NAME_PIVOTTABLE NAME;”
‘ Say we want to ignore PivotTable 1 on a sheet called Pivots as well as
‘ as ignore PivotTable 6 on a sheet called Graphs.
‘ We simply list them like this
‘ strExclusions = strExclusions & “Pivots_PivotTable 1;”
‘ strExclusions = strExclusions & “Graphs_PivotTable 6;”
‘ For this particular example, we will tell Excel to ignore PivotTable3 on
‘ the Other Pivots sheet
strExclusions = strExclusions & “Other Pivots_PivotTable3;”
‘————————————————————————————-
If strExclusions = “” Then strExclusions = “;”
varExcludePivots = Split(strExclusions, “;”)
strExclusions = “”
‘ Check if Master Pivot Table is either:
‘ 1. one PivotTables that we want the code to skip
‘ 2. in a worksheet that we want to skip
‘ If it is, we do nothing
varTest = Application.Match(pt_Master.Parent.Name & “_” & pt_Master.Name, varExcludePivots, 0)
If Not IsError(varTest) Then bDoNothing = True
varTest = Application.Match(pt_Master.Parent.Name, varExcludeSheets, 0)
If Not IsError(varTest) Then bDoNothing = True
If Not bDoNothing Then
‘ Add the master pivot to the list of pivots to exclude
lng = UBound(varExcludePivots) + 1
ReDim Preserve varExcludePivots(1 To lng)
varExcludePivots(lng) = Target.Parent.Name & “_” & Target.Name
For Each pf In pt_Master.PageFields
bCPL = False
i = 0
‘This is the CPL_Loop
Do
‘Since we can’t count the CurrentPageList, we have to iterate
‘through it and use the Error handler to handle runtime errors
On Error GoTo Err1:
If CPL_String = “” Then
CPL_String = pf.CurrentPageList(i + 1)
Else
CPL_String = CPL_String & “;” & pf.CurrentPageList(i + 1)
End If
i = i + 1
CPL_Loop:
Loop Until bCPL
CPL_Numbers_Array(pf.CubeField.Position – 1) = i
Next pf
CPL_String_Array = Split(CPL_String, “;”)
For Each ws In ThisWorkbook.Worksheets
‘Check to see if worksheet is listed in the “Excluded Worksheets” list
varTest = Application.Match(ws.Name, varExcludeSheets, 0)
If IsError(varTest) Then
‘It’s not in the list, so go ahead
For Each pt In ws.PivotTables
‘Check to see if PivotTabe is listed in the “Excluded Pivots” list
varTest = Application.Match(ws.Name & “_” & pt.Name, varExcludePivots, 0)
If IsError(varTest) Then
‘It’s not in the list, so go ahead
pt.ManualUpdate = True
‘Iterate through each PageField and set the items to those
‘items selected in same PageField of pt_Master
i = 0
For Each pf In pt.PageFields
If CPL_Numbers_Array(pf.CubeField.Position – 1) = 1 Then
With pf
.AddPageItem CPL_String_Array(i), True
i = i + 1
End With
Else
J = 1
With pf
.AddPageItem CPL_String_Array(i), True
i = i + 1
Do
.AddPageItem CPL_String_Array(i)
i = i + 1
J = J + 1
Loop Until J = CPL_Numbers_Array(pf.CubeField.Position – 1)
CPL_Loop2:
End With
End If ‘If CPL_Numbers_Array(pf.CubeField.Position – 1) = 1 Then
Next pf
End If ‘If IsError(varTest) Then *Excluded pivot table check
pt.ManualUpdate = False
Next pt
End If ‘If IsError(varTest) Then *Excluded worksheet check
Next ws
End If ‘ If Not bDoNothing Then
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
Err1:
If Err.Number = 9 Then
bCPL = True
‘Returns to the CPL_Loop label in the above code
Resume CPL_Loop:
End If
If Err.Number = 1004 Then
‘Returns to the CPL_Loop2 label in the above code
Resume CPL_Loop2:
End If
MsgBox Err.Description, vbCritical, “Whoops, something went wrong…”
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
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…..
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/
@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?
Office 2007
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.
Won’t the existing code do that? Just add the Employee field and the Item field to the exclusions list, so that those fields don’t get synchronized. Or am I misunderstanding you?
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?
Jeff, I am using 2007.
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
I liked the idea of using a button, but I can’t seem to make your code work. Thanks!
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 [email protected] if you can’t see this, or if the code doesn’t play well with Excel 2007, and i’ll help you out.
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.
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.
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.
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.
Alexander…what version of Excel are you using? Can you use slicers? Do you have any familiarity with named sets?
I’ve just started playing aronud with OLAP stuff, so can take a look at my routine at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ nd think how it could be amended to work on OLAP cubes.
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,
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.
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.
Jeff,
Any reason you are using the
Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
instead of the ThisWorkbookWorkbook_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.
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.
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/
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.
Rob
@Robin, thanks, and I’m glad the code will help you!
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?!
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.
Thanks!
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!
Thanks James! I’m glad the code is still helping people, and thanks for letting me know that it worked for you.
I have multiple pivot table on same sheet, and filtering with two criteria, Date and Department, the above code changing both Date and Department, is there is any way you can change the code, which only auto change “Date”.
Thanks
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 !
Thanks for sharing that solution — it might help someone else too!
I was thrilled when I first found this, but it seems the code doesn’t work for me.
I basicly have two pivot tables from different sources (using powerpivot). Does this not work if the pivot table data comes from OLAP/powerpivot ?
Hi there,
I am attempting to implement this code but I’m running into issues.
I have 6 pivot tables on the same spreadsheet. 4 are from one data source and 2 are from another.
They have a field in common that I would like to leverage as a filter but I’m struggling to do this using the VBA code above.
Unfortunately, there is no good way to re-stage the data to make it happen.
Any tips on how to use this code with pivot tables from 2 different data sources?
Thank you!!!
John
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?
@JP
I was having similar issues. I am using slicers to do the filtering and I am finding that if I create the slicer from the pivot table that has the data set with the most numbers, it works properly.
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 🙂
Hi Debra
Firstly, thank you very much for this helpful code. It solved a problem for me perfectly.
I re-wrote the code from scratch to better understand it, and along the way I made a few changes. The alternative version is below, and hopefully it might be helpful for someone.
Main changes:
* Tweaked to enable calling with both a Worksheet and a PivotTable, which means you can hook in to the Workbook_SheetPivotTableUpdate event rather than having to add code to every worksheet you create.
* Split the code into separate Subs, which should make it easier if anyone wants to affect just a subset of pivot tables, or even a subset of pivot fields.
* Removed
On Error Resume Next
and replaced it with slightly more robust error checking. This solved some problems I was having with the original version.* Tweaked the code to affect only PageFields on both the “source” and “destination” pivot tables. Not sure if this was the original intention?
* Changed the nesting of the loops: in my version it’s worksheet/pivot table/pivot field
* Removed
pf.ManualUpdate = True/False
: this resolved a problem with losing chart formatting, and removing the lines didn’t seem to cause any problems.This works well for me in Excel 2007, but I haven’t tested it in other versions. I hope it’s useful to someone.
Code to call routine – placed in ThisWorkbook module
Option Explicit
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, _
ByVal Target As PivotTable)
UpdatePivotTables Sh, Target
End Sub
Main routine – placed in a separate module, or in the ThisWorkbook module
Thanks Alastair!
You’re welcome Debra.
I noticed an error has crept into the code above. The following line:
If (ThisWorksheet.Name = SourceWorksheet.Name) Or (ThisPivotTable.Name = SourcePivotTable.Name) Then
should read:
If (ThisWorksheet.Name SourceWorksheet.Name) Or (ThisPivotTable.Name SourcePivotTable.Name) Then
In other words, the “equals” should be “not equals”.
And now I see how the error crept in! The less than/greater than signs are being stripped out! Let me try again:
If (ThisWorksheet.Name <> SourceWorksheet.Name) Or (ThisPivotTable.Name <> SourcePivotTable.Name) Then
Thanks Alastair, and I’ve fixed the code in your original comment.
Hi Alastair Bishop,
Please see my comment below;
http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/#comment-218960
Any assistance would be greatly appreciated!
Ben
Outstanding, this worked so easily. Thank you so much for sharing!
Hi Debra,
Thanks for this code – it has been extremely useful!
Can I go tweak this to not treat ptmain as the Target but instead a fixed Pivot Table?
The macro I am currently using updates my 8 pivot tables’ filters to be equal to whatever value is in a cell on my sheet. I had to define 8 different Pivot Tables and Pivot Fields, and then write out code to change each one individually which is cumbersome and undoubtedly slows my sheet down.
I want to leverage your macro so that when I change the value in my cell it updates only one of my PivotTables (which would be assigned to ptmain) and then your macro would detect a change to ptmain and kick in, hence updating the remaining 7 pivots.
Any help on this would be really appreciated!
Many thanks
Steve
Steve – one way you can do this fairly easily is by setting up a new PivotTable that masquerades as a Data Validation Dropdown, and putting the fields you want to sync into the Filters pane as per my post at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/. Let’s call it the ‘Master’ pivot. When users make changes to the master, the other pivots get updated. Because the master only has fields in the filters pane, it looks like a series of data validation boxes instead of a traditional PivotTable, but easily triggers Debra’s code.
If your PivotTables are big – or if you have many of them – then if users are only going to be choosing one item from each dropdown it will be much more efficient if you were to use an amended version of Debra’s code that gets triggered by a Worksheet_Change event than the PivotTable_Update event, because you could feed in the actual PivotField name that a user just changed, and have the code sync just that field across all the PivotTables concerned. Debra’s code above syncs all fields even though just one of them changed, and this can take some time on big PivotTables. That’s because Excel VBA doesn’t give the user much information regarding users’ interaction with PivotTables. You only get access to what’s called a PivotTable_Update event that tells you ‘Hey, this particular PivotTable had some kind of change made to it’ but it doesn’t tell you the nature of the change. And if that change was actually due to a user filtering it, the event doesn’t tell you what particular field was filtered – it only tells you that something changed in regards to PivotTable X. So you’re forced to sync ALL PivotFields any time any kind of change was made to that PivotTable, even if a user didn’t change a PivotField filter of interest. Even if they didn’t change any PivotField filter at all.
But following my approach above, and using a variation of the more efficient approach outlined at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ – means you can correctly identify the particular PivotField that changed, and then sync the other PivotTables with just that field.
I also happen to be working on some revised code that will intelligently works out whether the PivotTable_Update event was caused by a user filtering the Pivot, and then my code tries to work out by process of elimination which particular field got changed. My next step is to work on the macro that helps a user set this up on any workbook they want just by clicking a ‘SyncPivot’ icon in the ribbon and then following instructions. Ultimately this is going to be bundled into a commercial add-in, but I need some beta testers so flick me an email on [email protected] if you want to try it out.
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.
@Billy, you’re welcome, and thanks for letting me know that it helped!
Hi Doug
Thanks for this very enlightening info. I have a question for you, I have tried your code and it works well if you want to change the pivot filters on ALL sheets in the workbook. However I want to change the filters on all pivot tables ONLY in one sheet and leave the pivot tables on the other sheets unaffected. I tried manipulating your code but I made no progress. Do you have any suggestions on how to do this?
Thanks in advance!
Sorry, Debra! not Doug 🙁
And the answer is ABOVE. Sorry and thanks again!
Hi Debra,
Thanks for the tutorial.
If ws.Name & “_” & pt wsMain.Name & “_” & ptMain Then
This particular line, I would like to know why is there a need to compare the names of the main and other worksheets plus the “_” & pt
Hi Debra (All),
Did anyone ever work out how to resolve the chart formatting problem for Excel 2010, similar to Bretts post on January 9, 2013 at 2:15 pm. I have a primary (clustered column) and secondary axis (line with markers) that defaults the designs and colours back to default.
I have also tried removing pf.ManualUpdate = True/False from the code with no success.
Ben.
Thanks Debra Dalgleish for your original code and Alastair Bishop for your reworked code.
@Alastair Bishop, your code is exactly what I am looking for as it maintains the pivot chart formatting, however it has some peculiar results.
I have a consolidated pivot table (i.e. SourcePivotTable in SourceWorksheet) to determine all available periods (e.g. 201501, 201502, etc.) across 4 worksheets and separate pivot tables for each worksheet where the periods originated from.
If I select multiple categories (i.e. Periods) in the SourcePivotTable it selects the corresponding periods in the 4 pivot tables, even if that category doesn’t exist (perfect). However, in some instances if I select an individual category, it selects the correct category in some of the pivots and not in others (weird).
I would appreciate any assistance if possible!
Ben.
Thank you so much this helped me a lot.
Imane from France
This code works well but Excel crashes after clicking on Refresh Pivot Table?
Did anyone else had this problem?
Could you help please ?
I have only one sheet, multiple Pivot Tables with multiple filters, this code works good only when i choose one item.
Could you please tell which correction should i do to make this code work one multiple item selection too ?
I will really appreciate your help
Imane From France.
Hi Debra,
I am great admire of yours! May I ask u a quick question. I need to link one main slicer to many other slicer from different tables and different sources. So the idea is to click on a topic on the main slicer, which topic or label is present on the other slicer, then other slicer are activated as well.
Thank you for your help a lot.
Best,
Arben
Hi All
Thought I’d contribute with a version based on the original concept, but using the Workbook event
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
‘Synchronise all Workbook PivotTable PageFields to Source PivotTable
Dim wsSource As Worksheet
Dim ptSource As PivotTable
Dim pfSource As PivotField
Dim piSource As PivotItem
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
Set wsSource = Sh
Set ptSource = Target
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If (ws.Name wsSource.Name) Or (pt.Name ptSource.Name) Then
For Each pfSource In ptSource.PageFields
Set pf = pt.PivotFields(pfSource.Name)
pf.EnableMultiplePageItems = pfSource.EnableMultiplePageItems
Select Case pf.EnableMultiplePageItems
Case False
pf.CurrentPage = pfSource.CurrentPage.Value
Case True
For Each piSource In pfSource.PivotItems
pf.PivotItems(piSource.Name).Visible = piSource.Visible
Next piSource
End Select
Set pf = Nothing
Next pfSource
End If
Next pt
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Debra,
This is an amazing tool! I’m a complete novice with this, but was able to use your script to effectively tie different sources together…. buuuuuut, I was wondering if there is a way to make this work with PivotTables built off of a PowerPivot. Basically, I have a data set in my file, and then three other pivot tables connecting to different PowerPivots saved on a SharePoint website. These data sets are HUGE, so I have to connect to them through data connections on SharePoint. Is there a way to get this script to affect both the pivot tables connected to the data set saved in my file, and the pivot tables connected to the outside data source?
Great script!
I used to to easily allow me to easily create 40 versions of the same file with a different “MDA” selected across about 50 sheets for each version. I then use another script I pieced together from other helpful people to create a PDF and email it to the chosen “MDA.” The problem is that (1) each of the 40 files needs to refresh the same data from the SQL server and (2) any changes I make to the file need to be made 40 times. I would like to change your script to change all the tables in the document with the “MDA” field to one name. I will then insert it into the script below, putting it below the refresh statements and above the create/email PDF statements. Then I will copy that portion 40 times. Issues: Your script goes in the sheets, but the other script is a module. How do I tell your script the change all the “MDA” fields to my selected value. I have included the version of your script I am currently using and the create/email PDF script below.
**** Here is the module I want to insert your script into ***********
Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Dim FileName As String
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
ActiveWorkbook.RefreshAll
DoEvents
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
ActiveSheet.DisplayPageBreaks = True
Application.EnableEvents = True
‘ ******thought I would put version of your script here*******
FileName = RDB_Create_PDF(Source:=Range(“A1:n44″), _
FixedFilePathName:=”S:\AnesthesiaQA\Dashboards\Publication\B.pdf”, _
OverwriteIfFileExist:=True, _
OpenPDFAfterPublish:=False)
Set iMsg = CreateObject(“CDO.Message”)
Set iConf = CreateObject(“CDO.Configuration”)
iConf.Load -1 ‘ CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) _
= “owa.hospitalXXX.edu”
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
.Update
End With
strbody = “Anesthesiologists and CRNAs,” & vbNewLine & vbNewLine & _
“Attached is the Monthly QA Dashboard” & vbNewLine & _
“There is a department version and one specific to your patients. Please see me if you have any questions,” & vbNewLine & _
“DoctorB” & vbNewLine & _
“”
With iMsg
Set .Configuration = iConf
.To = “[email protected]”
.CC = “”
.BCC = “”
.From = “””Dr. B”” ”
.Subject = “Monthly Anesthesia Dashboard”
.TextBody = strbody
.AddAttachment “S:\AnesthesiaQA\Dashboards\Publication\B.pdf”
.AddAttachment “S:\AnesthesiaQA\Dashboards\Publication\Dashboard.pdf”
.Send
End With
Kill (“S:\AnesthesiaQA\Dashboards\Publication\B.pdf”)
‘Kill (“S:\AnesthesiaQA\Dashboards\Publication\Dashboard.pdf”)
‘ **** This is where I would copy and paste the 40 times your script followed by email/createPDF for each “MDA”*************
ActiveWorkbook.Close savechanges:=True
Application.Quit
End Sub
******** Here is the version of your script I currently have in my document on sheet1 and on ThisWorkbook **********
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & “_” & pt wsMain.Name & “_” & ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = “(All)”
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False
Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Very good post. I’m experiencing some of these issues as well..
Debra,
The formula works great however it only allows me to use two slicers that will accurately filter both tables. Once I click the other two slicers only one of the tables gets filtered. Is there any alteration to the code that would allow me to use more slicers?
I have really been struggling with a solution for this and am beginning to believe it really is impossible.
I have two pivot tables from two different data sources that cannot be combined because of the type of data (one source it detailed while the other is a high view of totals), however there is one column in both ranges which are identical “Departments”.
What I would like to do is filter “Departments” on Pivot1 and have it filter to the same department selection on Pivot2. Is it possible to manipulate this coding to detect and include a pivot table from a different cache?
Very grateful for any help with this.
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
Excelent! Thank you very much!
Great code.. Very very useful.. Thanks for saving my time…
Thanks so much!!! I’ve been looking for this code for so long. You made my day!!
Hello, and many thanks for the help on the code.
However I need your help on the below:
I have a master filter were I do my choices, then I have a sheet with several pivot that will automaticaly update when the master filter is selected. On the 3rd sheet, I also have several pivot, but on this sheet I need the general information, so I need to “exclude the code” for this sheets
How can I do it?
Thanks in advanced
Frederico, you can add a couple of lines to check the sheet name:
Hi all
Anyone having success using this brilliant functionality having Data Cubes as the source for the pivot tables ?
Best Regards
Lasse
Lasse: Can’t you just connect a slicer up to each of the PivotTables concerned? Or are they based on completely different data sources?
Hi,
This is a great bit of code. I was wondering if there is any enhancement to this whereby a pivot table returns a blank if the selection doesn’t appear ? I am selecting an individual person in my main pivot that exists in a number of other pivots, except one. This pivot returns all the data and I wanted it to return nothing (blank). Is this possible?
Thanks,
Chris
Hi,
Is there an easy way to update slicers on the subsequent tabs based on the selections from the first tab? I have duplicate slicers on each tab, which do not get updated as the page field filters are updated. I have numerous worksheets, all with pivots, but I want my users to be able to use the slicer on each tab (all different data sources, but with all common slicers).
Thanks
Bill
Bill: Take a look at my answer at this StackOverflow thread:
https://stackoverflow.com/questions/39599449/vba-to-connect-slicers-looking-for-improvements-to-code
Hi All,
What an amazing code, thanks you!
I’m a VBA rookie, wanted to know what should I do in order to apply this code only on a certain field?
In Debra’s example, i want to make all the pivot tables to change according to the employee field (that won’t happen now and I’m wondering why)
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.
Hi
I have 5 different sets of data on 5 different sheets which have two key fields that are common across all data sets in all the sheets
I used pivot tables in all the sheets and filter on the common field in each sheet. The data is the used to generate graphs and the scales for the graphs are calculated using Private Sub Worksheet_Calculate().
Earlier I used to filter each pivot table manually in each sheet. But with your code I am able to select an item in the pivot table in the active sheet and thee same item gets selected in the pivot table in all other sheets as well.
However, the graphs which are generated from the pivoted data get calibrated with the Private Sub Worksheet_Calculate() only in the active sheet. Only upon refreshing the pivot table data in any of the sheets do the graphs in the other sheets get re-calibrated.
What should be done so that I do not have to refresh the pivot table one more time?
I have tried ActiveWorkbook.RefreshAll which takes too long….
Pls suggest some solution.
Thanks
Hi, first thank you for such a beautiful code. Works like charm.
One issue I am facing. I have taken a Slicer based on the one of the Pivots. So, when I use the slicer, after everything gets updated, the sheets report filter gets selected by default.
I have tried putting the code to activate the 1st cell of the sheet but still the report filter cell gets selected. How to work around this?
Hi Debra,
this is so sinsightful,
is there any chance to use more than one slicer not only market but also other variables to be applied with your code?
regards,
Fariz