Change Pivot Table Date Filter to Cell Reference

[hhamond]

Hello

I need to do something really simple where I have many many pivot table in my report that required to updated every day
This involved changing the date filter

I have seen many posts on this however I always have issue with the identifying the correct PivotFields.
The data is obtained from Cube database and seems to have an array filter date.

Below is an example code where i just do the macro recording...


End goal is to change the date filter from [20-FEB-21] to a cell refence at H2

Thank youu



Sub Change_Date()
'
' Change_Date Macro
'


'
Sheets("Truck Usage").Select
ActiveSheet.PivotTables("Pivot_TruckHourly").PivotFields("[TIME].[TIME].[Year]" _
).VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivot_TruckHourly").PivotFields( _
"[TIME].[TIME].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivot_TruckHourly").PivotFields("[TIME].[TIME].[Day]") _
.VisibleItemsList = Array("[TIME].[TIME].[Day].&[20-FEB-21]")
End Sub

[mancubus]

welcome to the forum.

please use code tags when posting your code. (see my forum signature.)

dose this work for you?

ActiveSheet.PivotTables("Pivot_TruckHourly").PivotFields("[TIME].[TIME].[Day]") _
.VisibleItemsList = Array("[TIME].[TIME].[Day].&[" & Range("H2").Value & "]")

[mancubus]

using dates (especially non MDY format) may cause pain in VBA.

if above does not work, you may try below (or maybe stg else)

ActiveSheet.PivotTables("Pivot_TruckHourly").PivotFields("[TIME].[TIME].[Day]") _
.VisibleItemsList = Array("[TIME].[TIME].[Day].&[" & Format(Range("H2").Value, "DD-MMM-YY") & "]")