I would like to filter a Pivot Table based on the selection in a drop down menu.

I have found a solution using Slicers, but this solution doesn't work for me in this instance.

I have created a drop down menu using Data Validation in cell D4. I would like the selection from there to be carried into my Pivot Table.

I found the following solution, but it doesn't seem to be working (even though I think it should) ... https://stackoverflow.com/questions/...on-a-drop-down

Sub PivotChange(ByVal Target As Range)

 If Not Application.Intersect(Target, Sheets("PM Dashboard").Range("D4")) Is Nothing Then
        Sheets("PM Dashboard").PivotTables("PivotTable2").PivotFields("ProjectCodeName"). _
            ClearAllFilters
    
    Else
    
        Sheets("PM Dashboard").PivotTables("PivotTable2").PivotFields("ProjectCodeName").PivotFilters.Add _
     Type:=xlCaptionEquals, Value1:=Sheets("PM Dashboard").Range("D4").Value
 End If

End Sub
For my situation (I can't attach a sample file as the data is connected to a data warehouse):
  • PivotTable1 contains the list I use to make the Data Validation drop down in cell D4 (works great)
    PivotTable2 is where I want the selection from D4 to be applied
    The filter I want it to apply to is called "ProjectCodeName"
    All of my data and Pivot Tables are on a single sheet called "PM Dashboard"

I've attached a screenshot for reference showing how I want the drop down to interact with the Pivot Table (PivotTable2).

Drop down to PivotTable.png