selecting certain items in a pivot dropdonw in vba code

[charlie_c]

hi, I am trying to find code that will select certain dates in a pivot table dropdown after it has been refreshed. The dropdown will have boxes for dates and the code should select all the months after today's date or between certain dates. thx. Using the macro recorder I have the following, but the dates are hard coded and I need it to be flexible. thx.

Sub MacRecord()
    ActiveSheet.PivotTables("PivotTable1").PivotFields("need by").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("need by")
        
        .PivotItems("9/1/2017").Visible = True
        .PivotItems("10/1/2017").Visible = True
        .PivotItems("11/1/2017").Visible = True
        .PivotItems("12/1/2017").Visible = True
    End With
End Sub

 

[Oeldere]

maybe you can work with the formula eomonth(today()).

 

[Tinbendr]

something like

Sub MacRecord()
Dim Pi As PivotItem
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Need By").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1")
        For Each Pi In .PivotFields("Need By")
            If Pi.Value > Date Then
                Pi.Visible = True
            End If
        Next
    End With
End Sub

 

[charlie_c]

thx tinbendr. but I get a run-time error '438', object doesn't support this property or method in the "For Each Pi In .PivotFields("Need By")" line of code.

 

[Tinbendr]

I'll have to have a link to the file or sample file to proceed.

You can also email a sample file to my username @ gmail