change multiple pivot tables on 1 sheet

Hi all,

I have a sheet called "Report" which contains 3 separate pivot table all with the common field "Division". I would like the other 2 pivots to change to the Division I select from the field called "Division" on the first pivot table without having to select them manually.

Any help gratefully received.
thanks
x

Right click on your Report tab and choose View Code. Paste the following.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Not Target = ActiveSheet.PivotTables("PivotTable1") Then
Exit Sub
Else
'MatchPivots
Dim i As Integer, itemCount As Integer
    itemCount = ActiveSheet.PivotTables("PivotTable1").PivotFields(1).PivotItems.Count

    For i = 1 To itemCount
        ActiveSheet.PivotTables("PivotTable2").PivotFields("division").PivotItems(i).Visible = _
        ActiveSheet.PivotTables("PivotTable1").PivotFields("division").PivotItems(i).Visible
        ActiveSheet.PivotTables("PivotTable3").PivotFields("division").PivotItems(i).Visible = _
        ActiveSheet.PivotTables("PivotTable1").PivotFields("division").PivotItems(i).Visible
        

    Next
End If
End Sub

thank you MDBCT, much appreciated !
Lucy.