Pivot Table Change Event??
Posted by JAF on February 12, 2001 7:53 AM
I have a file with two worksheets - one contains source data, the other contains 3 pivot tables (I realise not the ideal way to set it up, but that's what the client has asked for!)
Each Pivot Table has the same page field (Month). Is there any way to run a macro whenever the page field on any of the pivot tables changes so that the other 2 pivot tables on the sheet always show the same month?
I guess that I'm looking for something like a Worksheet_Change event, but specific to the pivot tables.
Suggestions?
JAF
Posted by Mark W. on February 12, 2001 4:16 PM
JAF, if you don't discover a PivotTable change
event you might consider the following. Let's
assume that Sheet1!A1:B2 contains
{"Month","Sales";"Jan",100;"Feb",200}. Now
suppose that cell Sheet1!C1 contains "Sel" for
selector, and the formula, =A2=Sheet2!$A$2, has
been entered into cell C2 and copied down.
Your PivotTables reside on Sheet2 where Sheet2!A1:A2
contains {"Month";"Jan"}. The Page fields of these
PivotTables have been configured to "Sel" and the
2nd PivotTable was sourced from the 1st (i.e.,
"Another PivotTable" was chosen at Step 1 of 4 of
the PivotTable Wizard and PivotTable1 was chosen at
Step 2 of 4). Now all you'd have to do is change
the value in Sheet2!A2 and refresh PivotTable1.
PivotTable2 will conform.
Posted by Mark W. on February 12, 2001 4:19 PM
Oops! I almost forgot...
...The Page fields of these PivotTables have been configured with
"Sel" set to TRUE...
Posted by Mark W. on February 12, 2001 4:25 PM
Oh, yeah (c)...
You might be able to code an event that watches
cell Sheet2!A2 for changes and automatically
refreshes PivotTable1 for you.
Posted by Dave Hawley on February 12, 2001 6:16 PM
Hi JAF
This should work.
Dim pt As PivotTable
Dim MyDate As String
Dim sht As Worksheet
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Set pt = Me.PivotTables(1)
MyDate = pt.PageFields(1).CurrentPage
For Each sht In ThisWorkbook.Worksheets
On Error Resume Next
Set pt = sht.PivotTables(1)
pt.PageFields(1).CurrentPage = MyDate
Next sht
Application.EnableEvents = True
End Sub
Hope it helps
Dave
OzGrid Business Applications
