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.
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
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
Office, Excel, Microsoft, Microsoft 2010, Excel 2013