VBA to Refresh Pivots

[happyhungarian]

Hi, I have the following code that takes values selected via a list on a worksheet named "Trended P&L" that takes the value that's placed in cell D3 and uses that value to refresh a pivot table located on a worksheet named "Travel".

Private Sub Worksheet_Change(ByVal Target As Range)
Dim FTW As String
If Not Intersect(Target, Range("D3")) Is Nothing Then
FTW = Worksheets("Trended P&L").Range("D3").Value
Worksheets("Travel").Range("B2").Value = FTW
End If
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Travel").PivotTables("PivotTable1")
Set Field = pt.PivotFields("VP")
NewCat = Worksheets("Travel").Range("B2").Value

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



The issue I'm having is i'd like to have multiple cells used to refresh the pivot table. For example, rather have it just reference D3 on the Trended P&L tab, I would like it to reference D3:D5 and change the values on pivot filters in ranges B2:B4. However, when I try various methods it always errors out when trying to change the second filter.

Thanks for the help!!

Jesse