How to filter Pivot table based on a cell value - Microsoft Excel

Today I am going to share the VBA code which helps in filtering a pivot table based on cell value in Microsoft Excel.

This method helps you to set a value of cell and then once you click apply the pivot is filtered based on this value.

We will be using cell "C1" as the cell which will store the value to filter "Pivottable1" in the below screenshot. Our data is a 2x2 simple table. Prepare a similar view in your excel sheet with sheet named as "Sheet1".

Once you start excel. hit Alt+F11 and double click on the sheet in which you would like to filter the pivot table.

Now on the right hand side, paste the below code and save the file.

Sub Pivot_filter()
    Application.ScreenUpdating = False
  

    Dim pt1 As PivotTable
    Dim pf1 As PivotField
    

    Set pt1 = Worksheets("Sheet1").PivotTables("PivotTable1")

    Set pf1 = pt1.PivotFields("X")
    

    pf1.CurrentPage = ActiveSheet.Range("C1").Value
    Application.EnableEvents = True

Exit Sub

End Sub

Lets understand the code little more.

• Application.ScreenUpdating = False: This line ensures the user doesn't see the "real work" code is doing in the backend.
• Dim pt1 As PivotTable, Dim pf1 As PivotField: Here you are defining the pivottable and pivotfiled objects so that the macro understands them when used later.
• In the below to lines we are setting the actual names of Pivot Table and the corresponding pivot field which needs to be filtered by the code.
• Set pt1 = Worksheets("Sheet1").PivotTables("PivotTable1")
• Set pf1 = pt1.PivotFields("X")
• pf1.CurrentPage = ActiveSheet.Range("C1").Value: In this line we are pointing the code the cell where it needs to take the value and filter the pivot table.

Thanks for visiting. If you need a copy of the excel file readymade, please email me at askme.kkhelps@gmail.com