Filter date to update pivot table daily-CodePudding

I created a pivot table. Drag and place date into the filter field.

I am trying to automate the filtering of date to the previous working day so that the table will be updated daily.

Please help me thanks a lot!

CodePudding user response：

I can't help myself...

Base Data Used:

Pivot Table Layout:

Code Used:


' *** On Workbook Module ***
Option Explicit
Private Sub Workbook_Open()
    Call UpdatePivotTableDateToYesterday
End Sub

' *** In Module 1 ***
Option Explicit
Sub UpdatePivotTableDateToYesterday()
    Sheet5.PivotTables("PivotTable4").PivotFields("Date").ClearAllFilters
    Sheet5.PivotTables("PivotTable4").PivotFields("Date").CurrentPage = Format(Date - 1, "m/d/yyyy")
End Sub