Setting the filter value of all pivot tables

I have a spreadsheet with multiple pivot tables on multiple sheets.  Each Monday additional information is added to the data behind the pivot table.  I have created a script that will refresh all of the pivot tables, but I would also like for the script to update the filter value on all pivot tables to the most current date.

Thank you in advance for any help you can provide.
jmkbrownAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Are all the pivot tables pointed to the same source?

Will different pivot tables having different most current dates?

How you executed your scripts? By a button or when the workbook is opened?

Do you have a sample to upload for us?
Rob HensonFinance AnalystCommented:
In the data source create a column with a formula that determines whether the data for a particular row is Current or Previous.

In the Pivot Table then filter on Current rather than Date. When the Pivots refresh they will change to valid filter already.
jmkbrownAuthor Commented:
Attached is a sample of the spreadsheet.  All tables are pointed to the same source.  They will all need to have the same date.  The script is manually executed.
SampleClass.xlsm
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

Sub AllWorkbookPivots()

    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim pf As PivotField
    Dim MaxValueDate
    MaxValueDate = Format(Application.WorksheetFunction.Max(Sheets("Data").Range("C:C")), "M/dd/yyyy")
    MaxValueDate = Replace(MaxValueDate, "-", "/")
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
            Set pf = pt.PivotFields("UpdateDate")
            With pf
                .ClearAllFilters
                .ClearValueFilters
                .CurrentPage = MaxValueDate
            End With
        
        Next pt
    Next ws
    
    ActiveWorkbook.Save
End Sub

Open in new window

SampleClass-b.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmkbrownAuthor Commented:
This worked perfectly!  Thank you very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.