Solved: Multiple Pivot Tables - simultaneous field change

[mike_trotter]

I have multiple pivot tables looking at different views of the same data.

Each pivot table has the same options available in the pivot table 'page' area. Is it possible to link the 'page' fields so that if I change the view in a field in one pivot table, it is changed automatically in all the other pivot tables too? Currently I need to manually change all the tables to match the fields.

Any ideas out there?

Thanks!

[rory]

Are the pivot tables all on the same sheet or on different sheets, and do you want all the pivot tables in the workbook to show the same field?

[bradh_nz]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' fire the macro.
Set KeyCells = Range("F2:F5")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' change pivot settings when cell in range has changed

'Refresh All Pivot Tables
ActiveWorkbook.RefreshAll

With Sheets("SheetName")
.PivotTables("Issue_Data").PivotFields("Period").CurrentPage = Range
End With

End If
End Sub

[mike_trotter]

Q: 'Are the pivot tables all on the same sheet or on different sheets, and do you want all the pivot tables in the workbook to show the same field?'

A: They're all in the same sheet, but there is another pivot table in the same workbook that I want to remain independent (for now).

[mike_trotter]

bradh_nz, guessing you're a Kiwi in London??

I've tried the following:

Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' fire the macro.
' b58 is the "Provider" field in one of the pivot tables that I want the other tables to match to
Set KeyCells = Range("b58")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' change pivot settings when cell in range has changed
'Refresh All Pivot Tables
ActiveWorkbook.RefreshAll
With Sheets("Summary Report")
.PivotTables("PivotTable1").PivotFields("Provider").CurrentPage = Range
.PivotTables("PivotTable2").PivotFields("Provider").CurrentPage = Range
.PivotTables("PivotTable3").PivotFields("Provider").CurrentPage = Range
.PivotTables("PivotTable4").PivotFields("Provider").CurrentPage = Range
.PivotTables("PivotTable5").PivotFields("Provider").CurrentPage = Range
End With
End If
End Sub

but with my extremely limited vba skills can not get it to work...

any suggestions? can I send you the file to take a look at? (it's large...)

[rory]

Try using the Worksheet_PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim varPage, pt As PivotTable
On Error GoTo err_handle
Application.EnableEvents = False
varPage = Target.PageFields("Provider").CurrentPage
For Each pt In Me.PivotTables
pt.PageFields("Provider").CurrentPage = varPage
Next pt

reset:
Application.EnableEvents = True
Exit Sub
err_handle:
Resume reset
End Sub

[mike_trotter]

Brad H

I did a google to find out how to enter a private sub macro, and your solution now works - was my incompetence all along!

thanks very much to all