How to make Todays Date visible on Pivot Table Filters

[anne.gomes]

Hi,

I have two pivot tables which get updated daily by the source data worksheet. What I want to do is select and "tick" the current date for these two worksheets.

I have this code so far which doesn't work...

Sub Macro1()
'
' Macro1 Macro
'
'
Dim TodayDate As Date
TodayDate = Date
Sheets(Array("Queue Trend", "Due Date Analysis Trend")).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .PivotItems(TodayDate).Visible = True
    End With
End Sub

The error is in this line

.PivotItems(TodayDate).Visible = True

Can someone please help me with this? Do I need a for loop?

Thank you in advance

[anne.gomes]

I did it this way, which worked fine

I'm sure you can make it much simpler..

Sub RefreshPivotDate()
'
' Macro2 Macro
'
'
    
    Sheets(Array("Queue Trend", "Due Date Analysis Trend")).Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
        Type:=xlBottomCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Count of ID"), Value1:=23

    
        
End Sub