I want select dates within a range based on inputting a Start Date and End Date in cells B1 & B2.
If I input Jan 1 and Jan 31, it should update the pivot table to select all dates within that range.
I tested the code for a small simple pivot and it worked.
My actual pivot comes from an external source and gets to the point where it clears all date filters but it doesn't select all of the dates within the range I specify.
I recorded a macro to update some dates to see how the code behaves:
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]").VisibleItemsList = Array( _
"[XXX Show Dates].[Date].&[1950-01-04T00:00:00]", _
How can I get past the clear filters and update the date ranges?
Sub PivotFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem
Dim StartDate As Date
Dim EndDate As Date
Sheets("VIP only").Activate
StartDate = Cells(1, 2)
EndDate = Cells(2, 2)
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]").ClearAllFilters
Set pvtF = Worksheets("VIP only").PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]")
For Each pvtI In pvtF.PivotItems
If DateValue(pvtI.Name) >= StartDate And DateValue(pvtI.Name) <= EndDate Then
pvtI.Visible = True
Else
pvtI.Visible = False
End If
Next pvtI
End Sub