Pivot Tables - Connected

[Guest]

Excel 2003:

I have 5 pivot tables - pivots 2-4 are connected to pivot-1 so that when I
refresh any pivot, they all refresh.

Each pivot has only one page field. In any given pivot, I can either choose
from the drop-down or type in an accepted value for the page field, and that
particular pivot will update based on that page field value. This does not
affect the page field values for the other pivots.

The page field value cannot be a formula.

Question: Is there any way to set up these 5 pivots (either with pivot table
features or through VBA) so that when I choose a page field value in Pivot-1,
the other pivots will also revert to that same page field value? For
example, if I set Pivot-1 page field value to 'cat', then the page field
values for pivots 2-4 will automatically change to 'cat'?

Thanks in advance for any help you can provide ...

William 3

 

[Roger Govier]

Hi

Download the following file from Debar Dalgleish's site, and this will
show you how to do it, with some code that you can then modify.
http://www.contextures.com/PivotMultiPagesDiffChange.zip

 

[Guest]

Thank you Roger ... I'm going there now.

Hi

Download the following file from Debar Dalgleish's site, and this will
show you how to do it, with some code that you can then modify.
http://www.contextures.com/PivotMultiPagesDiffChange.zip

 

[Guest]

Hi again Roger,

The code I downloaded didn't work on my machine for some reason (version
compatibility?). However, it gave me the idea I needed. Here's all i takes:

Right click on the tab where the pivots exist > 'View Code':

Private Sub Worksheet_Calculate()
Cells(2, 6).Value = Cells(2, 2).Value
End Sub

Need to tighten it up a bit, but that's the basics. Thanks again!