PivotTable, page filter - filtering with date

Question

I am trying to filter Page filter which has date values. But I keep getting error when I use .PivotFilters.Add type:=xlBefore, value:="5/1/2015". I also tried the code below:

Dim nd As Long
nd = CLng(Date - 90)

pvt.PivotFields("startdate").ClearAllFilters
pvt.PivotCache.Refresh
pvt.PivotFields("startdate").PivotFilters.Add Type:=xlBefore, Value:=nd

But now I am getting Named argument not found.

The PivotTable is created successfully but not filtered.

Answer 1

When a pivot field is used in the Report Filter filtering it is different than when it's a Row or Column Label. See the code below to set your filter criteria on the startdate located in the Report Filter.

Dim nd As Date
nd = CLng(Date - 90)

Dim pvt As PivotTable
Set pvt = Sheet1.PivotTables(1)

With pvt

    .PivotCache.Refresh

    Dim pf As PivotField
    Set pf = .PivotFields("startdate")

    With pf

        .ClearAllFilters

        Dim pi As PivotItem
        For Each pi In pf.PivotItems
            If pi < nd Then pi.Visible = True Else: pi.Visible = False
        Next

    End With

End With