Updating pivot filters from fields

[Dalewo]

Hi All

I know this question has been asked to death so i have followed a guide here: http://www.mrexcel.com/forum/excel-...ot-table-filter-based-value-another-cell.html

The problem is i want to update a number of filters from fields, to do this i have tried to copy the VBA twice with a few tweaks but still didnt get anywhere. The first Filter 'Fin_YR' works fine, the second does nothing beside refresh the page....

It would be good to include it all in one macro but my knowledge doesnt go that far any help is appreciated

Private Sub Workbook_SheetChange(ByVal Sh As Object, 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, Worksheets(1).Range("H1:H2")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim pivot_item As PivotItem
Dim NewYear As String
Dim test_val As String


'Here you amend to suit your data
Set pt = Worksheets(1).PivotTables("TestTable1")
Set Field = pt.PivotFields("Fin_YR")
NewYear = Worksheets(1).Range("H1").Value


'Here is the test if the input field exists
test_val = NewYear
For Each pivot_item In pt.PivotFields("Fin_YR").PivotItems
    If pivot_item.Name = test_val Then
        Exit For
    End If
Next pivot_item
On Error Resume Next


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


End Sub


Private Sub Workbook_SheetChange_Acct(ByVal Sh As Object, 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, Worksheets(1).Range("H1:H3")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim pivot_item As PivotItem
Dim AcctMgr As String
Dim test_val_acctMgr As String


'Here you amend to suit your data
Set pt = Worksheets(1).PivotTables("TestTable1")
Set Field = pt.PivotFields("Account Manager")
AcctMgr = Worksheets(1).Range("H2").Value


'Here is the test if the input field exists
test_val_acctMgr = AcctMgr
For Each pivot_item In pt.PivotFields("Account Manager").PivotItems
    If pivot_item.Name = test_val_acctMgr Then
        Exit For
    End If
Next pivot_item
On Error Resume Next


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


End Sub