Excel 2007 : Change Multiple Page Fields Simultaneously in Pivot Tables

Hello everyone !

I have a problem with pivot tables in Excel 2007.

There are 4 pivot tables on 2 sheets, all made from same source table (after I made first one I copied it and changed the parameters I needed).

I'd like to change filters in page fields simultaneously on all 4 tables. Ie. when I change page field filter on the first one I'd like to have page fields changed on other 3 pivot tables.

I found some solutions here: http://www.contextures.com/excelfiles.html#Pivot

However when I tried to apply solutions in templates PT0021 - Change All Page Fields and PT0008 - Change Multiple Page Fields , they just wouldn't work. Moreover, I can't even make original templates to work. This also happens with the other files and macros I downloaded (and they all have more or less similar approach )

I know they are macro-enabled files so I enabled macros in Excel (and developer tab and whatnot) but that didn't help.

There is obviously something I am doing wrong or not doing at all or there is something else I should enable in order to make macros in templates to work..
I guess if I can make template files work I could use them for my pivot tables.

Any help or idea is appreciated.

eriol

The code in the link below is specific to Pivots created in XL2007 and beyond*:

http://www.excelforum.com/2155223-post20.html

The code should be placed within ThisWorkbook in VBE

The array vFields should contain the names of those Page Fields common to each Pivot that are to be altered simultaneously.

Without a file it's hard to give any more specific advice I'm afraid - hopefully the above will do what you want.

*pivots created pre XL2007 handle multi select Page fields differently (the process for iterating selected items within the Page field is very different in earlier versions)

Assuming you have no Pivots to be excluded from this process then first add an additional variable declaration:

Please Login or Register  to view this content.

The modify the main iteration to:

Please Login or Register  to view this content.

(in short the first and last lines are new - the 2nd line is modified to use ws rather than ActiveSheet)

My bad... I was in a rush when I replied last time.

The code below should reside in ThisWorkbook and should replace all other code already in place
(ie remove existing code from ThisWorkbook and from the Sheet1 object in VBE)

I've also changed the order of the iteration.

Please Login or Register  to view this content.

Again, the above is coded for Pivots created in XL2007 and beyond.

Johnnylzep,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.