Macro_nerd
New Member
- Joined
- Oct 19, 2023
- Messages
- 3
- Office Version
- 365
- Platform
- 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.
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")