Selecting date ranges for pivot table from external source

Viewed 43

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
0 Answers
Related