If you have many pivot table with filters, applying changes to them all can be time-consuming. Fortunately, there is an easier way! Using a macro in Microsoft Excel, you can quickly change all the filter settings at once. At the end of this article you be able update your pivot table filters at once with VBA. How awesome is that?. Let’s get started.
Example Use Case of Changing All Pivot Table Filters at Once.
For two reports I create monthly, I have approximately fifteen worksheets all with pivot Tables. None of these pivots are controlled by a slicer (at the design request of the user) but all pivot tables need to be updated every month to show the new monthly figures, I really do not want to have to change all of the pivot table filters month to month individually, so this Excel VBA Macro is super useful.
What Does The Macro Do?
This macro changes all pivot table Filters in all pivot tables in your active workbook. Specify in the VBA code the value of the filter.
How Does The Macro Work?
Step 1. Open Visual Basic, Insert A Module and Name Your Macro.
Firstly, open the Visual Basic Editor. There are two ways to do this
- Hit ALT+F11 or
- Developer Tab | Visual Basic
Insert a new module into the current Excel workbook by selecting This Workbook, Insert Menu then Module.
So, the final part of step 1 is to name your Excel macro. Type Sub and then name of the macro. In this example the name of the macro is ChangePivotFilter. Accordingly, Excel automatically inserts the End Sub line of code. All other lines of code are stored between these two lines.
Step 2. Declare Variables.
Next, the second step is to declare any variables in the macro. Declaring a variable creates a memory container for that value. In this case example variables are declared for worksheets and pivot tables which Excle will loop through in the code. The declared variables can be seen below.
Dim WS As Worksheet, Dim WB As Workbook, Dim myPivot As PivotTable, Dim myPivotField As PivotField
Step 3. Set Code To Run On Current Workbook.
This line of code ensures only the current active workbook is updated with this code. No other Excel workbooks will be affected by running the VBA code.
Set aWB = ActiveWorkbook
Step 4. Loop Through All Pivot Table Filters
Next any current pivot table flters are cleared. In addition, any errors that occur Excel moved to the next line of code. The for next loop is used to loop through all pivot table filters, resetting them in preparation for the next line of code.
For Each WS In aWB.Worksheets
For Each myPivot In WS.PivotTables
Set myPivotField = Nothing
On Error Resume Next
Step 5. Set The Pivot Table Filters
The next line of code is where the pivot table filter is updated with VBA. In this example the filters change ‘Sales Person’ to ‘Julie’. Change this line of code the the filter required. Each worksheet and pivot table is looped until all pivot table filters are updated. Finally, the procedure then ends. The final part of this process is to test the macro.
Set myPivotField = myPivot.PivotFields(“Sales Person”)
myPivotField.CurrentPage = “Julie”
Next myPivot
Next WS
End Sub
Finally, copy the code below, all you need to do is replace the name of the Pivot Table Field and the Pivot Table Filter.
Sub ChangePivotFilter()
Dim WS As Excel.Worksheet, aWB As Excel.Workbook, myPivot As Excel.PivotTable,myPivotField As Excel.PivotField
Set aWB = ActiveWorkbook
For Each WS In aWB.Worksheets
For Each myPivot In WS.PivotTables
Set myPivotField = Nothing
On Error Resume Next
Set myPivotField = myPivot.PivotFields(“Sales Person”)
myPivotField.CurrentPage = “Julie”
Next myPivot
Next WS
End Sub
So, changing all the filters in your pivot tables at once can save you a lot of time, and this macro makes it super easy. Be sure to sign up for my newsletter so you don’t miss any more tips like this, and subscribe to my YouTube channel for more helpful videos.
