How to link cell value as a filter in PowerPivot Pivot Table in Excel?

Question

I have two Pivot Table (PivotTable1 and PivotTable2) in Sheet PivotTables_Sheetand a cell that shows the year in Sheet YearInput. I tried to link the cell year (J4) in YearInput as a filter to all pivot tables in PivotTables Sheet.

But somehow my code doesn't work as expected. Anybody knows how to correct this code? This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Target.Address = Range("J4").Address Then Exit Sub
     Dim pt As PivotTable
     Dim ptItem As PivotItem
   On Error Resume Next
  For Each pt In Worksheets("PivotTables_Sheet").PivotTables
    With pt.PivotFields("Year for Sales")
        If .EnableMultiplePageItems = True Then
            .ClearAllFilters
        End If
        Set ptItem = .PivotItems(Target.Value)
        If Not ptItem Is Nothing Then
            .CurrentPage = Target.Value
         End If
      End With
  Next
End Sub

Note: I use Excel 2010 and built the pivot tables using PowerPivot.

If you remove On Error Resume Next, what error messages, if any, do you get? — BigBen
It says "Unable to get the Pivot Fields Property of the PivotTable Class". But I have checked and the field name matches correctly. — user71812

Marcus Marcus · Accepted Answer · 2018-09-17T17:07:09

One way to do this without VBA is to use a slicer which is common between all the pivots. Then one year is changed on one, it will also update the other pivot tables.

This is by first clicking on the pivot, which will then show on the ribbon an Analyze option. Under that there is an insert slicer, which you would do for year.

Afterwards you would go to your other pivots and instead do filter connection, and select your year slicer.

Note that the year slicer can then just be moved to a hidden sheet if you do not want the end user to see it, it will still do its job of propagating the year across all the pivots that are referencing it.

How to link cell value as a filter in PowerPivot Pivot Table in Excel?

1 Answers