I want to start by saying I'm not very good with VBA and so I'm stuck on a small issue for a macro I'm trying to create which will select dates within a range based on inputting a Start Date and End Date in cells B1 & B2. So if I input Jan 1 and Jan 31, it should update the pivot table to select all dates within that range. The code currently looks as shown below. I tested code for a small simple pivot and it worked. The issue I'm having is that my actual pivot comes from an external source and gets to the point where it clears all date filters but it doesn't actually select all of the dates within the range I specify. I tried to record a macro to update to some dates to see how the code behaves and it gave me this:
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]").VisibleItemsList = Array( _
"[XXX Show Dates].[Date].&[1950-01-04T00:00:00]", _
Any idea how I can get the below code to get past the clear filters and actually 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
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]").VisibleItemsList = Array( _
"[XXX Show Dates].[Date].&[1950-01-04T00:00:00]", _
Any idea how I can get the below code to get past the clear filters and actually 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