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

sanantonio

New Member
Joined
Oct 26, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hi All.

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

VBA Code:
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.
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,127
Messages
5,835,553
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top