Pivot Table Issues

How to Change the Reporting Month in Multiple Excel PivotTables with VBA

Phi Hung VoPhi Hung Vo Nov 3, 2026 869 views

Question details

Update the Report Month filter automatically across multiple PivotTables using a worksheet-change VBA macro whenever a specific date cell is modified.

How to Change the Reporting Month in Multiple Excel PivotTables with VBA
Product
Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
The user wants to synchronize the 'Report Month' filter across several PivotTables based on a date inputted into a specific cell, avoiding manual filter adjustments.
Observed behavior
Without a script, users must manually update each PivotTable filter. Basic VBA attempts result in runtime errors if the input date does not exist in the source data.
Before you start

Ensure you have the Developer tab enabled in your spreadsheet application, and remember to save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve your VBA code.

Solution 1Recommended

Use a Worksheet_Change VBA Macro

Automate the filter update across all PivotTables by placing a change-event script with proper error handling in the target worksheet module.

This solution utilizes the Worksheet_Change event, meaning the code triggers automatically when a specific cell (e.g., cell B1) is modified. It loops through all PivotTables on a designated source sheet and applies the new date filter.

To prevent VBA runtime errors when a selected date is missing from the data, the code includes an 'On Error GoTo' statement.

1
Access the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Open the Target Worksheet Module

In the Project Explorer pane on the left, double-click the worksheet where you will enter the date (e.g., 'Sheet1' or 'Run').

3
Paste the Macro Code

Copy and paste the following code into the code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim pvt As PivotTable Dim field As PivotField Dim reportDate As Date If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub If Not IsDate(Me.Range("B1").Value) Then Exit Sub reportDate = CDate(Me.Range("B1").Value) On Error GoTo InvalidMonth For Each pvt In Worksheets("Source").PivotTables Set field = pvt.PivotFields("Report Month") field.ClearAllFilters field.CurrentPage = reportDate pvt.RefreshTable Next pvt Exit Sub InvalidMonth: MsgBox "The selected reporting month is not available in the PivotTables.", vbExclamation End Sub

4
Test the Macro

Close the VBA editor. Type a valid date into cell B1 of your 'Run' worksheet and press Enter. The PivotTables on your 'Source' sheet will automatically update.

Use a Worksheet_Change VBA Macro
Adjusting Sheet Names: Make sure to change the word 'Source' in Worksheets("Source") to the actual name of the worksheet that contains your PivotTables.

Automate Your PivotTables with WPS Spreadsheet

WPS Office fully supports VBA macros, PivotTables, and Slicers. You can execute advanced Excel scripts and manage complex reporting tasks easily and for free.

  1. 1. Open Your File in WPS Spreadsheet: Launch WPS Office and open your workbook containing the PivotTables.
  2. 2. Enable the Developer Tab: Navigate to the top ribbon, click on the Developer tab, and select 'VBA Editor'.
  3. 3. Insert Your Script: Double-click your desired worksheet in the Project window, paste your macro code, and save the file as a Macro-Enabled Workbook (.xlsm).
Full support for Excel VBA macros and Worksheet_Change eventsAdvanced PivotTable features including Report Connections and SlicersHighly compatible with Microsoft Excel (.xlsx and .xlsm formats)Free, lightweight, and fast performance for large datasets
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA code throw an error when I select a certain month?

This happens if the selected date does not exist in the PivotTable's source data. You must add an error handling block like 'On Error GoTo' to catch and manage invalid filter criteria without crashing the macro.

Do I have to refresh the PivotTables manually after the VBA runs?

No, you do not. You can include 'pvt.RefreshTable' in your VBA loop to automatically refresh the data in each PivotTable right after the filter is applied.

Can I synchronize multiple PivotTables without using VBA?

Yes. You can insert a Slicer for your date or month field and use the 'Report Connections' feature to link that single Slicer to multiple PivotTables simultaneously.