Hi All:
I've attached a sample file. I have multiple pivot tables on one sheet and am connected to a cube for my data. I want to be able to change the filter on the first table and have it change the other filter on the other pivots. i have other filters for each table but i will only be changing the transaction date filter. I have to be able to have multiple dates selected so it's not just one item that will be selected.
Thanks for your help!!
Cjax
Last edited by Cjax; 04-11-2011 at 02:53 PM.
Does anyone have any ideas? I've looked through the archives but nothing seems to be working very well.
Hi Cjax,
I think I can solve this but am running into PivotTable Event problems. I'll give it another stab by tomorrow I should have an answer.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Cjax,
See if one of these will help you get started in the right direction:
Debra Dalgleish has a download at her site which shows how to do this.
http://www.contextures.com/excelfiles.html
Scroll down to Pivot Tables and download
http://www.contextures.com/PivotMultiPagesChange.zip
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Ok Cjax
Here's the story. I have a trigger event on cell B1 of your Pivot Sheet. That is the Filter you are supposed to change. You change this one filter of Pivot Table 1 and the other pivot tables will match.
After a lot of trial and error and error and error it finally worked. I'm sure one of the smart gurus could clean up my code to about the half the lines.
The real problem is that no pivot table can have all its multi select filter boxes to off. So if you stumble into this condition you get an error.
Find the attached with the code that works to make Pivot Table 2 and 3 match the filters used on the Transaction Week of Pivot Table 1.
Tomorrow I'll go study Stanley's links to see if the do what I want to, in an easier way.
Let me know if I did what you wanted.
Hey Marvin! Thanks for your work on this. I'm going to check it out now and see if it's what i was thinking of.
I'm also going to check out the links your provided Stanley.
Thanks to both of you!
Ok - So the file that you sent works fine. However, when I take that code and put it into my workbook, which is being fed it's data from a cube, it doesn't work. Is it the outside data source that's causing the problem?
My code was very specific to the Pivot Table Number. Click on each pivot table and try to see if you are on PivotTable1, PivotTable2 or PivotTable3. You may need to adjust which two tables are "downstream" from the first. You would have to go into the VBA code and change/fix the pivot table numbers.
Hope this works for you.
MarvinP,
Very nice.
This goes in my archives.
Have a great day, and weekend,
Stan
Hi again Marvin:
I changed the names of the pivot tables in the code when I first put it into my workbook. i believe the problem is that these tables are OLAP Pivot Tables.
Hi everyone - I'm close this out - Thanks to both Marvin and stanley for the help.
The solution provided here works for multiple pivot tables where the data is stored in the workbook. It does not work when your retrieving data from a cube.
Thanks!
Chris
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks