Filtering Pivot Table by Dates up to Today

[WhollyUnqualified]

I have a single, large (about 17K lines) data export from a database which populates about a dozen pivot tables. Most of these are primarily filtered by a "Required Date" column, which changes daily.

I need to select the current date and all dates in the past, which up until now has involved a great deal of manual "clicking" on my part. I am attempting to automate this process using VBA code, but am having issues figuring out how to select all dates (in my "Required Date" field) up to AND including TODAY.

Any suggestions would be greatly appreciated. Thanks!

 

[tonyyy]

Wholly...

The following might get you started...

Sub FilterPivotTableDate()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim d8te As Date
    
    Set ws = ActiveSheet
    d8te = Date
    
    For Each pt In ws.PivotTables
        pt.PivotFields("Required Date").ClearAllFilters
        pt.PivotFields("Required Date").PivotFilters.Add _
            Type:=xlBeforeOrEqualTo, Value1:=d8te
    Next pt
End Sub

Cheers,

tonyyy

 

[WhollyUnqualified]

Tonyyy,
Thanks for your help! I had a heck of a time getting it to work, until some research pointed me to the fact that the .PivotFilters.Add function does not work when the filter is in the Report Filter field. I needed to move "Required Date" to the Row Labels field, and the code you provided worked great.

Thanks again!

WhollyUnqualified

 

[tonyyy]

You're welcome. Glad it worked out...