I'm having a heck of a time trying to figure this one out so thought maybe someone could help me.
Basically I'm trying to have a Pivot Table Report filter update based off a user selection in a drop-down data validation cell (vs. having to update it in the pivot table menu itself).
I've tried various VBA codes and none have come even close to working, there has got to be a really simple way to do this, any suggestion?
Here's the skinny:
My Sheet4 Cell B2 has the drop-down data validation list of entries.
The Pivot Table is called PivotTable1
The Pivot Table field name for the filter I'm trying to change is called HFM Management Reporting Entity
Literally nothing happens when I make a change to the drop-down in cell B2, please advise.
The code I've tried using is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")
Set Field = pt.PivotFields("HFM Management Reporting Entity")
NewCat = Worksheets("Sheet4").Range("B2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
|