VBA Help

[happyhungarian]

Hi, I need some help with the following VBA. I'm using this to update pivot filters based on cell values located in H6:H7. As the values in this range are updated it automatically updates the filters on the pivot. However, the auto update doesn't work if H6:H7 are formulas themselves. If the value updates the pivot tables wont update unless the cells are physically touched. Is there a way to fix this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("H6:H7")) 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("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Category")
NewCat = Worksheets("Sheet1").Range("H6").Value

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

End Sub

 

[happyhungarian]

Or is there another macro that would work instead of this?