Pivot Table - Custom Date Filter

[sbrotz]

I am attempting to create a macro that will create a custom date Filter that will mimic the selection of "date filter/after". Below is the code developed from using the macro recorder:

ActiveSheet.PivotTables("Results").PivotFields("Original Date"). _
ClearAllFilters
ActiveSheet.PivotTables("Results").PivotFields("Original Date"). _
PivotFilters.Add Type:=xlAfter, Value1:="10/1/2014"

What I am after is to subsitute the "10/1/2014" date with an entry that will calculate the start date as 60 days prior to todays date. The example below errors - what am I doing wrong?

ActiveSheet.PivotTables("Results").PivotFields("Original Date"). _
ClearAllFilters
ActiveSheet.PivotTables("Results").PivotFields("Original Date"). _
PivotFilters.Add Type:=xlAfter, Value1:="=TODAY()-60"

 

[vavs]

I just tested this so I hope it works for you.

I added a variable called Minus

At the head of the macro add:

Dim Minus as Date

Minus = Now() - 60

then change the code on the last line to:
PivotFilters.Add Type:=xlAfter, Value1:=Minus

HTH

 

[sbrotz]

Tim - works perfectly! Thanks for your response.