Looking to Set All pivot table filter fields to same selection using VBA

[Findrishabh]

Hi team,

I am comparatively new to VBA , however I keep trying to solve some ideas that come into my mind, recently I am trying to sync filter fields of 2 or more pivot tables using vba but I am not able to

I have currently two Pivot tables named "Dashboard1" on sheet1 and other "Dashboard2" on Sheet2,
they all have common filter field named "Time" which has time in intervals like "12:00:00", 01:30:00 and so on.

here is how my code looks
I am using this code on each sheet to call pivot table
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'If Target.Name = "Dashboard1" Then (this is for sheet 1)
' Call PivotFilters
' End If
'End Sub

similar change event on sheet 2 to call PivotFilters2

here is my sub (which changes to set pt1 value to pt2 and in sub 2 to set pt2 value to pt1)
Sub PivotFilters()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim pt1 As PivotTable, pt2 As PivotTable
Dim filterfld As String, filterval As String

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

Set pt1 = ws1.PivotTables("Dashboard1")
Set pt2 = ws2.PivotTables("Dashboard2")
filterfld = "Time"

If pt1.PivotFields(filterfld).CurrentPage <> "(All)" Then
filterval = pt1.PivotFields(filterfld).CurrentPage
Else
filterval = "(All)"
End If
pt2.PivotFields(filterfld).CurrentPage = filterval


End Sub


this currently allows only 1 selection under filter field "Time" but I want that whatever values selected in Pt1 should be applied to pt2 and vice versa on detecting change,

I have tried "EnableMultiplePageItems=true" but that does not work, please help me?

 

[jkpieterse]

If the pivots share the same source you can attach both pivots to the same slicer and they will sync filters automatically: Excel: Pivot Table Slicers Explained