Set the date filter on yesterday on opening of pivot table

[Shamimo]

Hi,

I have a pivot table linked to a warehouse that displays sales Values.

I would like that on opening of the pivot table, the date filter for the report is automatically set to yesterday's date instead of displaying the last saved date.

I have never used a VBA code and tried to do it with an inserted calculated fields but with no avail.

Can someone help me please ?

Thanks a lot for taking the time.

 

[Gerald Higgins]

You can create a macro that runs automatically whenever the file is opened, as long as macros are enabled.
All that is needed is to name the macro as "Autpen".

This code appears to do what you want . . . .

Sub Auto_Open()
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
        Type:=xlDateYesterday
    
End Sub

 

[tonyyy]

All that is needed is to name the macro as "Autpen".

Nifty. Adds flexibility over "Private Sub Workbook_Open()"

Thanks for sharing!

tonyyy

 

[Shamimo]

Thanks Gerald! Cheers