Thread: Linking Pivot table filter criteria

Hi All, I have 2 pivot tables (PivotTable1 and PivotTable2) which both source from the same pool of date.

The difference between the two are that I have a different report filter criteria.

For one the heading is "day", for the other it is "night". Each colum of data has the same entry range (names of staff) and I want to be able to link the two tables so for instance -

If i select "Paul" from the (day) report filter on PivotTable1, I want the (night) report filter on PivotTable2 to also change to "Paul".

Is this possible?

Sure, but it requires VBA. Don't know if that meets your requirement.

If you search the Forum you will find countless threads related to controlling multiple pivots simultaneously - there are (IMO) differing layers of complexity pending version and required actions...

Here at least the requirements "seem" straightforward enough... ie a single page/report filter with a 1 / All type approach where data source is identical, on which basis perhaps something like:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, strField As String
strField = "FIELDNAME"
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each ptTable In ActiveSheet.PivotTables
    If ptTable <> Target Then
        ptTable.PageFields(strField).CurrentPage = Target.PageFields(strField).CurrentPage.Value
    End If
Next ptTable
ExitPoint:
Application.EnableEvents = True
End Sub

it's not clear to me if the page filter is sourced from the same field or not ?
if so, simply change that above in red to reflect the required field name... if not you should I think be able to adapt the above as required by adjust strField as appropriate

Note: the above also assumes PTs are located on the same sheet... unspecified

(the above code would reside in the Sheet Object on which the PTs exist - right click sheet name in XL - View Code - paste above into resulting window)

Good one DO,

Lot of dependencies (requisites), but I like the small code. Thanks for that.

OK, so it seems PivotTable1 uses "day" field (?) and PivotTable2 uses "night" field, correct ?

If so, we adapt such that pending Target the strField is adjusted accordingly such that if PivotTable1 is altered then we set strField to be "day" and vice versa, so perhaps try:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, strField As String
strField = IIf(UCase(Target.Name)="PIVOTTABLE1","day","night")
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each ptTable In ActiveSheet.PivotTables
    If ptTable <> Target Then
        ptTable.PageFields(IIF(strField="day","night","day")).CurrentPage = Target.PageFields(strField).CurrentPage.Value
    End If
Next ptTable
ExitPoint:
Application.EnableEvents = True
End Sub

above is obviously untested...

If the fields do not contain the same names then obviously the above is open to error... if they do I guess we would ask why you're using different fields in the first instance...