How do I get Pivot filter change event to fire?

[Claude]

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

 

[Claude]

Boy oh boy, am I ever feeling lonely. Other posts seem to
get a reply, but mine sits here all by its lonesome.

Have I done something wrong?

Claude

 

[Debra Dalgleish]

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

 

[Mark Bigelow]

Hi Claude,

I have a macro that does just what you are looking for, in a report I
run here at work. Here's what I do:

In the pivotTable_Change event, you'll have to loop through each of the
slave pivotTables and uncheck all of the filtered items, except the one
that matches the one selected in the parent PivotTable. This done this
way:

With Sheets("Dump
Sheet").PivotTables("PivotTable3").PivotFields("POSWeek")
On Error Resume Next
.PivotItems(Range("B2").Value).Visible = True
For Each itm In .PivotItems
If InStr(strDate, itm) > 0 Then
itm.Visible = True
Else
itm.Visible = False
End If
Next itm
On Error GoTo 0
End With

It's certainly not the most elegant way to do it, but it works for me.
If somebody knows of a better way, please post it!

Mark
---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Claude]

Thank you both, Debra and Mark.
Now I can go to the beach without worrying about this.

Claude