Filter PivotFields Containing String

[ckrawiec]

I have a pivot table and I'd like to filter by pivotfield: "Inspectionitem". I'd originally done it like this:

'data worksheet and cache defined up here
Set pt1 = ActiveSheet.PivotTables.Add(cacheofpt1, Range("A1"), "DP_Electrical")
 
'put the feilds in
With pt1[INDENT]'blahblahblah other pivotfields here[/INDENT]
 
    With .PivotFields("Inspectionitem")
        .Orientation = xlPageField
        .Position = 1
        .PivotItems("General Characteristic for Road Test").Visible = True
        On Error Resume Next[INDENT]'.pivotitems("abc").visible = False    
End With[/INDENT]
End With

I could go through every pivotitem and define .visible to be true or false, but there are well over 100 pivotitems and I don't want to go through all of them. I came up with the following to help. It's supposed to make all pivotitems containing the string "auto" visible. The problem is it never recognizes a pivotitem containing the string, even when I hold the cursor over p and I can see that it does.

Sub pivot()
Set pf = ActiveSheet.PivotTables("DP_Electrical").PivotFields("inspectionitem")
For Each p In pf.PivotItems
    If p.Name = "*auto*" Then
    p.Visible = True
    End If
Next p
End Sub

If I change p.Name to just p, the same problem occurs.

Any help is greatly appreciated! Thanks in advance

 

[ckrawiec]

I've solved my own problem. See below for successful code:

With mypivottable
     'add other fields here
     With .PivotFields("filteringpivotfield")
           .Orientation = xlPageField
           .Position = 1
           .ClearAllFilters
           .EnableMultiplePageItems = True

            For Each p In .PivotItems
                   If p.Name Like "*string*" Then
                         p.Visible = False
                   End If
            Next p
      End With
End With