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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,216,000
Messages
6,128,202
Members
449,433
Latest member
mwegter95

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