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
Application.ScreenUpdating = False
Dim pt1 As PivotTable
Dim pf1 As PivotField
Dim pf1 As PivotField
Set pt1 = Worksheets("Sheet1").PivotTables("PivotTable1")
Set pf1 = pt1.PivotFields("X")
pf1.CurrentPage = ActiveSheet.Range("C1").Value
Application.EnableEvents = True
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.
