VBA Macro with option to filter Pivot Tables multiple times.

Macro_nerd

New Member
Joined
Oct 19, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I need help fixing this code to filter a Pivot table with multiple criteria.
I think something is wrong in the For Loop. Please help.

VBA Code:
Sub FilterPivotTable(ByVal fieldName As String, ByVal filterValue1 As String, Optional ByVal filterValue2 As String = "", Optional ByVal filterValue3 As String = "")
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim filterValue As String
    Dim filterApplied As Boolean
    
    ' Set a reference to the pivot table
    Set pt = Workbooks(PM_PLANNER_WB_NAME).Worksheets("PM TRAVEL PIVOT").PivotTables("PMTravelPivot")
    
    ' Set a reference to the pivot field (specified column) you want to filter
    On Error Resume Next
    Set pf = pt.PivotFields(2)
    On Error GoTo 0
    
    If pt Is Nothing Then
        MsgBox "Pivot table 'PMTravelPivot' not found.", vbExclamation
        Exit Sub
    End If
    
    ' Set a reference to the pivot field (specified column) you want to filter
    On Error Resume Next
  Set pf = pt.PivotFields(Col_Num(fieldName))
    On Error GoTo 0
    
    If pf Is Nothing Then
        MsgBox "Field '" & fieldName & "' not found in the pivot table.", vbExclamation
        Exit Sub
    End If
   
    
    ' Loop through pivot items to find and apply the filter
    For Each pi In pf.PivotItems
        filterValue = pi.Value
        If filterValue = filterValue1 Or filterValue = filterValue2 Or filterValue = filterValue3 Then
            pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=filterValue, Value2:=filterValue2
            filterApplied = True
        End If
    Next pi
    
    If Not filterApplied Then
        MsgBox "Filter value(s) not found for field '" & fieldName & "'.", vbExclamation
    End If
End Sub

Sub Main () 
 Call FilterPivotTable("SBusUnitDesc", "Text1")
     Call FilterPivotTable("SProjGroupDesc", "New", "Relocation", "Remodel")
      Call FilterPivotTable("SCategory", "ACT")
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,706
Messages
6,173,998
Members
452,542
Latest member
Bricklin

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
Back
Top