Update Date Filter for Multiple Pivot Tables Simultaneously

[npetrisis]

I have about 10-15 pivot tables that I would like to apply a date filter to based on a date range. I have a script for a macro that will do this when the date is classified as a pivot field in the row labels section of the pivot table, and it will read the start value (cell H1) and end value (cell H2) for the dates I want and apply this filter accordingly. This is the script:

With ActiveSheet.PivotTables("PivotTable1")
' Remove any existing filter
.ClearAllFilters
' Add a filter for the date range
.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
Value1:=CStr(Range("H1")), Value2:=CStr(Range("H2"))
End With

However, when date is moved to the Report Filter field in the pivot tables, this script will not work because date is not recognized as a pivot field. I get the error "Run-time error 1004: Application-defined or object-defined error". My pivot tables are designed with the date in the Report Filter, so is there a way to apply this date range filter logic so that all my tables will be impacted simultaneously?

Thanks

 

[Scott Huish]

Duplicate post. See http://www.mrexcel.com/forum/excel-questions/862394-apply-date-filter-multiple-pivot-tables-simultaneously.html