VBA to select Pivot field choices

[Joe C]

ActiveSheet.PivotTables("Targ1").PivotFields("PO_TITLE")

I want to make a simple Loop through the this pivotfield selecting anything that has "MTR" as it last three characters. I am having the hardest time geting it to work. Has anybody done this.

 

[Jerry Sullivan]

Joe,

You can try the function below.

Private Function Filter_PivotField_wWildcard(pvtField As PivotField, _
        strPattern As String)
    Dim strItem1 As String, blTmp As Boolean
    Dim i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    With pvtField
        For i = 1 To .PivotItems.Count
            blTmp = .PivotItems(i) Like strPattern
            If blTmp Then
                strItem1 = .PivotItems(i).Visible = True
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox "No Pivot Items match filter specification."
            Exit Function
        End If
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        .PivotItems(i).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) Like strPattern Then
                .PivotItems(i).Visible = True
            Else
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Function

It can be called like this...

Sub TEST()
    Filter_PivotField_Wildcard _
        pvtField:=ActiveSheet.PivotTables("Targ1").PivotFields("PO_TITLE"), _
            strPattern:="*MTR"
End Sub

If you have a large number of PivotItems to step through,
you can speed up the processing quite a bit by testing each item's Visible state
before trying to set it to True or False.
However with smaller lists this won't make a noticeable difference.

 

[Jerry Sullivan]

Oops...I changed the function name after testing. It should be called with this name.

Sub TEST()
    Filter_PivotField_wWildcard _
        pvtField:=ActiveSheet.PivotTables("Targ1").PivotFields("PO_TITLE"), _
            strPattern:="*MTR"
End Sub

 

[Joe C]

Works like a charm. Thanks