repost...updating page fields on muliple sheets simultaneous

[JMH022]

Posted: 2002-03-29 08:18
--------------------------------------------------------------------------------
I have a workbook with multiple pivot tables (one on each worksheet). They are all based on the same source, but each pivot is displaying a different piece of data about the employee.

The SHEET1,PivotTable1 is the "main" or "primary" pivot table. On SHEET2 and SHEET3 are the secondary Pivot Tables.

Is there a way to change the "Employee" page field of the secondary tables to match the primary table's page field? I can program a command button by using the code below, but I would rather have all sheets update as the primary sheet's page field is changed:

Sub UpdateSheets()

Dim sLoc As String
sLoc = ActiveSheet.PivotTables("PivotTable1").PivotFields("EMPLOYEE").CurrentPage

Application.ScreenUpdating = False

Sheets("SHEET1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("EMPLOYEE").CurrentPage = _
sLoc

Sheets("SHEET2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("EMPLOYEE").CurrentPage = _
sLoc
Application.ScreenUpdating = True

End Sub

The Worksheet_Change event sounds like what I need, but I can not figure out how to get it to work right. Can someone help me work this out?