The following code is adapted from a posting by Robert Rosenberg. It
changes the second Pivot Table if the page is changed on the first PT.
You could revise it to suit your layout. As noted in the code, place the
code on the module for the worksheet which contains the Pivot Tables.
Dim mvPivotPageValue As Variant
Private Sub Worksheet_Calculate()
'by Robert Rosenberg 2000/01/11
''I use a module level variable to keep track of
''the last selection from the Page Field.
''This routine was place in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable
Dim pvt2 As PivotTable
Set pvt = Me.PivotTables(1)
Set pvt2 = Me.PivotTables(2)
If LCase(pvt.PivotFields("Customer").CurrentPage) _
< LCase(mvPivotPageValue) Then
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Customer").CurrentPage
pvt2.PageFields("Customer").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
End If
End Sub
Claude wrote:
I need to match the filters (if that's what the drop-down
boxes on the pivot table are called) on a number of pivot
tables. That is, if the filter on one is changed (for
simplicity, let's assume there's a master pivot), then the
other three pivot tables should also match that filter.
In more concrete terms, if a user selects "February" on
the drop-down in the master pivot, then the slave pivots
should all display the data for February also.
I'm having a heck of a time getting Sheet_PivotTableUpdate
to fire, and I'm not even sure that's the right event to
be looking to trap. The documentation "assumes" that a
Sheet object has been defined in a class module, and I
can't find documentation on how to do that (sometimes
search is wonderful, other times it gives me nothing
useful).
I have reasonable experience with VBA after command
clicks, but really am a newbie when it comes to
this "event handling." Thanks for any help (in
advance)... I'm going on vacation shortly, so if I don't
respond quickly, it's not for lack of appreciation, it
just means power got restored in NY and my flight took off.
Claude
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html