View Single Post
Old 06-25-2017, 01:51 PM   #28
flamesrule_kipper34
Franchise Player
 
flamesrule_kipper34's Avatar
 
Join Date: Aug 2008
Location: Calgary, AB
Exp:
Default

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
flamesrule_kipper34 is offline   Reply With Quote