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?
--------------------------------------------------------------------------------
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?