control Excel PIVOT tables contains a cell value with VBA

[harnovandijk]

I have created attached pivot table and created below VBA. But this gives me only the a result if the value in H6 is equal to a value in the Pivot. I need the result in the Pivot that contains the value entered in H6, So in my case if I put the word "sales" in H6 it should give me "Sales Flavors" and "Sales Fragrances".



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Business Unit")
NewCat = Worksheets("Sheet1").Range("H6").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub