Filter multiple pivot tables based on 1 cell value / VBA formula

[Rey99]

Hi all!

Here is my question, any help would be much appreciated!

I have 3 pivot tables on a sheet that is referring to the same data.

I'm entering the contract number I would like the results for in cell A2, for example WQ19. How can I have all the filters ''contract'' in each pivot table updated to this contract number or any contract number entered and show the results.

I have this formula that work for 1 pivot table but as soon as I add another pivot table on the same sheet, the formula is not working and I don't know how to fix it.

Here is what I have for 1 pivot table;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Dim Sh As Worksheet, Pt As PivotTable
For Each Sh In Worksheets
For Each Pt In Sh.PivotTables
With Pt.PivotFields("contract")
.ClearAllFilters
.CurrentPage = Target.Value
End With
Next Pt
Next Sh
End Sub


Thank you !

 

[Rey99]

Anyone can help ? Thanks!