Controlling Multiple Pivots

I think we'd need more info - is the field a Report Filter field, Row Label / Column Label etc... if Report Filter do you have multi selection enabled for ex. ?
(ie in earlier version of XL the Page Field aka Report Filter was a single select - still the default in 2007 but can be adjusted)

Where are the PT's located ?

Are there any other PTs in the Workbook that are not related ?

If all stored on one sheet the same question but for that one sheet only...

What you want to do can be done but the more info we have the better...

Here's a quick sample file with three pivots on one sheet where changing the specified field on any one of three pivots updates the other two accordingly. The code resides in the Sheet Object (ie in VBE see Pivot sheet object), though by no means the finished article it should help I hope.

The file uploaded is in 2007 format given the slight differences in PT functionality between 2007 and earlier versions.

I told them it wasn't a God complex...

Wisdom ? No not really... what I know I've sourced almost entirely from Forums like this and of course the Macro Recorder... I've never owned a book and my code at times highlights this fact unfortunately.

Best way to get into VBA is to think of something you do presently that takes far longer than you would like and try to work out how you could automate it... work from there ...

IMO no-one should ever be too proud to use the macro recorder as it's an excellent tool to quickly determine the core syntax, like most user-friendly utilities the Macro Recorder invariably generates superfluous code but what it does show you are the vital bits upon which you can base your "streamlined" code.

I've been coding best part of ten years and what I don't know far outweighs that which I do...

Hey DO
Another follow up doubt on controlling multiple pivots with one single entry. You had suggested the following code ( and I had mentioned that we will only have single entry in the record filter)

You had suggested the following code and it worked very well.... Now i just realized there is actually a need for multiple selection in the record filter. How should I change this code to reflect that.. (i.e I choose multiple entry in a record filter of one pivot and all pivots will get updated with the same multiple entry selections)

Thanks much
:-)

Please Login or Register  to view this content.

sailor64, please note:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

please edit your post per the above

I am breaking my own rule here as I am going to be going offline soon and may not be able to post back but based on the original sample file you could perhaps try:

Please Login or Register  to view this content.

May be better methods.

I have added tags to your last post on this occasion though please ensure you adhere to rules going forward. Apologies to all for "bending the rules" here.

In reality I think I would need to see the file in question... I am presuming of course it is XL2007 format (I don't believe you can't select multiple page items other than in OLAP PT's in earlier versions)

If you can't post the file here directly given confidentiality issues you can PM me and I will give you an address to which you can mail it - for me eyes only etc - confidentiality assured...

To be honest I didn't experience any real issues but I would alter the code such that instead of repeating all of the code for each Field you iterate the Fields running the same code...

Please Login or Register  to view this content.

...in the above you can see that rather than repeating the big block of code for the 2nd field I have put the fields of interest into an Array (vFields) and subsequently iterated that Array repeating the code.

And to clarify, the above worked for me without issue on your file.

I'm going to need to see the file in full... I can't think why you're going into a loop given the disabling of events etc ... what happens if you run the code I provided on the file you provided (rather than your real file), it worked for me without incident - can you confirm likewise ? This will help narrow down possible issues.

I will pm you - I need to see the file with my own eyes.

Re: ! ... .xlsm files have ! in the icon

In DO's absence...

Yes, but please start a new thread, (cross-referencing this one) asking exactly that question.
The reason we do this is it makes it easier for others searching the forum to find an answer to their questions - on that basis, please title your thread carefully to describe your need.

Hi DO,
This code worked perfectly for what I was trying to accomplish on a report. However, I'm trying to find out what should I do to control two pivot tables with different source data from the same filter on the same sheet. In Excel 2010, I was able to accomplished this with the slicers, but when I loaded the file at work (Excel 2007) the slicers went away because of the compatibility issue. I would send the file, but I this point it's rather huge due to all the data in it.
So, let me specify a bit more, the pivot tables are in spreadsheet "OVERALL SUMMARY" and the data is located in two different sheets "DATADUMP" & "DATADUMP2"
The data used in the filter is the same (month number), but it comes from two different tables uploaded from a database. I'm just trying to make it easier for the user to change the month on any particular pivot table and reflect the data that belongs to that month on all the tables.
I hope that you get this message since it has been quite some time since last post on this thread.

Thanks,

Otto

==============================
Never mind... today 7/28 I opened the report and it seems to work the way I wanted with the previous code.

VKS,

What do you want to post here? A solution or a question of your own?

VKS,

What do you want to post here? A solution or a question of your own?