Hello, time for some Macro fun on a Monday. Today I want to share with you a a small Macro I use a lot as its really simple and saves me tonnes of time.
For two reports I create monthly, I have 14 + sheets all with Pivot Tables. None of these Pivots are controlled by a slicer (at the design request of the user) but all 14 + Pivots 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 Macro is so handy.
Download Macro Mondays Example Files.
What Does The Macro Do?
This Macro changes the Pivot Table Filters in all Pivot Tables in your active workbook, to whatever value you specify in the code.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module
Step 1. We need to declare some variables, so Excel creates a memory container for each of the Pivot Tables and Worksheets it will loop through.
Dim WS As Worksheet
Dim WB As Workbook
Dim myPivot As PivotTable
Dim myPivotField As PivotField
Step 2. We ensure that only the current active workbook is used in this procedure, and clear all of the Pivot Table filters. Any errors that occur we move onto the next line of code
Step 3. This step sets the Pivot Table field we going to use as Sales person and sets the value of the Pivot Filter to Julie in this example.
Step4. Excel then loops through the next Pivot Table and next worksheet until all have been changed and the procedure ends.
Step 5. Test you macro!!!!
You can download the code here, all you need to do is replace the name of the Pivot Table Field and the Pivot Table Filter.
Dim WS As Excel.Worksheet
Dim aWB As Excel.Workbook
Dim myPivot As Excel.PivotTable
Dim 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”
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.