Update All Pivot Table Report Filters Simultaneously With The Same Date

Assume you have a Drop Down List on Cell A2 on worksheet named Sheet1. As soon as you change the Drop Down Value, you want all the Pivot Tables on the Workbook, using that value in the Report Filter to update automatically.

Put this code on a regular Code Module -

----------------------------------------------------------------------------

Sub UpdatePivot() Dim I, WS As Worksheet, PT As PivotTable For Each WS In ThisWorkbook.Worksheets For Each PT In WS.PivotTables With PT For I = 1 To .PivotFields.Count If .PivotFields(I).Orientation = 3 And .PivotFields(I).Caption = "InvoiceDate" Then .PivotFields("InvoiceDate").ClearAllFilters .PivotFields("InvoiceDate").CurrentPage = Sheets("Sheet1").Range("A2").Text End If Next End With Next PT Next WS End Sub

----------------------------------------------------------------------------

And put this code on the Worksheet Module -

----------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Sheets("Sheet1").Range("A2")) Is Nothing Then Call UpdatePivot End If End Sub

----------------------------------------------------------------------------

Important Note: Values in the Report Filters are stored as Text values even though the underlying data may have values from the same column using other data types like Date, Numbers etc.

The code will only work when the Formatted Value of the Drop Down List and the Value on the Pivot Report Filter match exactly (For example in case of a Date, the screenshot shows the usage of DD-MMM-YYYY Date Format, which will need to be similar on the report filters as well)

Try changing the Value From the Drop Down List, and see how all the Pivot Table Report Filters update that value

#PivotTables #ReportFilters #PivotTableReportFilters #VBA #Excel

Follow Us
  • Twitter Basic Square
  • Google+ Basic Square
Recent Posts
Search By Tags
No tags yet.