PDA

View Full Version : [SOLVED] How to make Todays Date visible on Pivot Table Filters



anne.gomes
06-25-2014, 11:40 PM
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
06-25-2014, 11:57 PM
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