Multiple pivots with the same row - how do i make them change at the same time?

Perhaps:

Please Login or Register  to view this content.

If not, post a sample.

I've tried combining the 2 sets of code above to achieve this but cannot get it to work.

Please post a small sample file which illustrates all PT's in place and their respective structures. I won't look at it today unfortunately but will do so tomorrow.

Is it possible to adapt this so it only happens on a button click too please? I have other macros to add in more data fields and when i run these the code below runs and slows things down.

I would argue it's better to keep this tied to the PT event itself else PT functionality and results become detached which would be odd... better to toggle events in your "other" code as and where appropriate such that the PT update event does not fire in first instance.

Just quickly... you make reference to "multiple page field items" which leads me to believe you're running this in XL2007 specifically - is that correct ?

If so it would IMO be best to upload a 2007 file ... reason being PT functionality differs between 2007 and earlier versions - for example pre XL2007 there is no multi select Page filter functionality ... as such the VBA for controlling PT's will differ between versions and it makes sense to code exclusively in the version you're using. Of course if it's your intention to use the file in multiple versions then it's a different matter.

davellll,

here is some code that should work if (and only if) the Pivots are created in XL2007 file (ie not in compatibility mode)

Please Login or Register  to view this content.

In the above code I have as per your code comments consolidated all of the fields into the one process (ie Page Fields and Row Fields)

I've attached a working example (.xlsm as outlined above)


EDIT:
I should add you can still use multi page item functionality in PT's created in earlier versions but it requires moving the Page Field to the Row Field, making selections, moving back to Page Field... invariably be a pain.
If running earlier PT's in 2007 you can use MultiPageField selection but in code terms the visible status of the pivot items in the page field is not available to the other PT's ... so to mimic you would need to (I suspect) action the above
(ie move the field in both Target and subsequent PT's from Page to Row to ensure appropriate selections and then move back to Page Field

Ah ok... I understand now - that makes sense... in my (late) edit to prior post I think I highlighted the issue you will face on that front... I'm in the midst of something else right now but I will run some tests on another client machine on which I'm running 2002 and I'll see what I can come up with re: hidden page items and replication thereof.

As things stand I think you can in essence disregard the prior code for the time being (unfortunately).

Ignoring issue of multi page item filters for the time being the below should do the remainder... ie can replace your existing code.

Please Login or Register  to view this content.

Not sure how badly this will perform but the below works on my 2002 version and will cater for multiple page item selection etc...

Please Login or Register  to view this content.

I've had a look at this more thoroughly now and the problem is actually quite an obvious one but one that I did not account for in testing...namely items which exist in one PT may not exist in another ... and this could be caused by a number of issues...

a) values are simply different

b) legacy items exist in the item listings

Now point b) is an interesting one and one that made me think a little more about this... in general given you want to give each PT the most opportunity of being identical you need to ensure that when you refresh one PT you refresh them all... however the act of Refresh is different to making selections etc (ie the former is based on a premise that underlying data has altered) yet refreshing via the Refresh button will

1) only refresh the relevant cache

2) invoke the update event

given the possibility of problem a) we then need to try and restrict the opportunity for error by not invoking the update event on a standard refresh...how to do this ?

Well I thought about it and came up with the idea of disabling/creating some custom controls on the right-click PT menu, ie into ThisWorkbook:

Please Login or Register  to view this content.

and then into a new module

Please Login or Register  to view this content.

The code for the refresh All should for sake of clarity reside in a new & separate module

Please Login or Register  to view this content.

What all of the above will do is ensure you can only refresh All PT's simultaneously via the right-click menu and "new" option added namely: Refresh All ... and
when this is done the BeforeUpdate event is NOT invoked (it will also remove "legacy" items from any/all lists)

Now the above still does not resolve the problem that given the data sources for each PT are different it's still quite possible that items will exist in one PT but not in another, for ex. you may run an Edit->Replace on all which columns but forget to update the last one meaning the same options do not exist in all PT's ... this may be highly unlikely however it is still a possibility.

On that basis you then need to think about adding handlers around the setting of item visibility so I would change the existing Select Case section to

Please Login or Register  to view this content.

And all of that together *should* I think resolve your issue(s) ?

Beware!

I have found another little problem with this macro that is easy enough to fix. If your data has blanks in it the pivot table will have a pivot item called "(Blank)" in it, but of course your actual data is not "(Blank)" it is "". Therefore the other pivot tables will not update if you wish to hide/show blanks.

So my solution is to simply find and replace all "" with "(Blank)" within all the pivot tables data regions.

Also beware of sorting your various pivot tables differently as this will effect them being 'lined up' i found that sticking to a manual sort was the safest option for my data. Changing to ascending seemed to stop the macro working.

At last I can actually contribute a little something rather than asking Qs!

DaveL