Control Pivot report filter with change of cell content?

[Matrixx2525]

Hello all,

I'm trying to control a reportfilter "KPI" in a pivottable, and the control should be with the content of a cell H6 on another sheet.
The sheetname of the pivottable is "Pivots", sheetname of controlling cell H6 is "Data".
Now, on dedicatedexcel.com I already found a VBA solution, which works with Worksheet_SelectionChange, and it works because it checks for a manual change in cell H6:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 is touched
If Intersect(Target, Range("H6")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Pivots").PivotTables("PivotTable1")
Set Field = pt.PivotFields("KPI")
NewCat = Worksheets("Data").Range("H6").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

Now, the problem is that the contents of cell H6 is NOT a value entered manually, but a formula (="H20"). Also cell H20 is controlled not manually but with other formulas.
Because the content of cell H6 is a formula, above VBA does not work because that VBA waits for a cell to be touched physically by the user.

Does anybody know how I can change the report filter of the pivot, using the RESULT of the formula in cell H6, instead of waiting for somebody to click on the cell H6?

Thanks a million for your help guys and girls!

Best wishes,

Jeroen