Auto Pivot Filtering VBA that can be used for multiple pivots on one worksheet

[sanantonio]

Hi All.

Don't claim to be the owner of but have made significant use of:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("AA2:AA3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("by Vendor").PivotTables("Vendor_by_PO")
    Set xPFile = xPTable.PivotFields("Vendor")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

AA2:AA3 is the filter parameter (IE: What we are filtering to). I only filter to one thing, not sure why it always has to refer to two cells but it's how the code came.

"by Vendor" is the worksheet name, "Vendor_by_PO" is the pivot table name and the "Vendor" is the criteria being filtered.


A a recent development at work means I now need to auto filter two pivot tables on the same worksheet. The above works for one pivot table, quite merrily filtering to whatever Vendor number is inserted into AA2 and AA3 (Which a separate macro does as it fires off each vendor's stats into weekly pdfs. But the second one won't budge. I'm 100% sure that the second pivot table is set up exactly correctly, IE: the criteria is the same, the name is correct, the worksheet name is correct etc. all the silly little things that could be wrong.

I went back and found the page where I originally got this code from online and people were asking the same question whether they could use it multiple times on the same worksheet.

Does anyone have a solution to make it work multiple times? Or a different code that will auto filter a pivot table based on a cell reference that does work with multiple pivots?

For clarity to this code is in the worksheet itself, not activated by a button. So literally it's automatic when the AA2/AA3 cell reference is changed, this must remain the case.

Any help would be greatly appreciated.