VBA to Amend the Filter Selection on a Pivot Table Based on a Drop Down List

The secret to changing a filter multiselect is that first you have to turn everything "on" and then turn off what you don't want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet         ' Sheet Name
Dim pt As PivotTable        ' Pivot Table Name
Dim pf As PivotField        ' Pivot Field Name
Dim pi As PivotItem         ' Pivot item pointer

' Do only if cell B1 is changed
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

' Initalize variables
Set sh = Sheets("Sheet1")
Set pt = sh.PivotTables("PT_Month")
Set pf = pt.PivotFields("Month No")

' Turn everything on
For Each pi In pf.PivotItems
    pi.Visible = True
Next

' Turn off what we don't want
For Each pi In pf.PivotItems
    If pi.Value > Target.Value Then
        pi.Visible = False
    End If
Next

End Sub

The reason it fails is because Pivot Filters treat numbers and dates as strings. .

Here is the modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet         ' Sheet Name
Dim pt As PivotTable        ' Pivot Table Name
Dim pf As PivotField        ' Pivot Field Name
Dim pi As PivotItem         ' Pivot item pointer

' Do only if cell B1 is changed
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

' Initalize variables
Set sh = Sheets("Sheet1")
Set pt = sh.PivotTables("PT_Month")
Set pf = pt.PivotFields("Month No")

' Turn everything on
For Each pi In pf.PivotItems
    pi.Visible = True
Next

' Turn off what we don't want
For Each pi In pf.PivotItems
    If CLng(pi.Value) > Target.Value Then
        pi.Visible = False
    End If
Next

End Sub

There is a slightly different plan of attack. This code loops through all the sheets, finds whatever pivot tables are on the sheet and resets the filter. If you add pivot tables that don' use Month No, then this code will break.

If you add other pivot tables, then the code would have to be modified. I would recommend telling the spreadsheet what sheets / tables need updating in a table that can be on a hidden sheet. You can loop through the contents of this table to get the sheets and pivot tables by name. The core code - turn everything on and then turn off what you don't need - will remain the same.