Pivot report filter with wildcard

[evaem]

Hi everyone!Using pivot tables in Excel, you can filter the report fast and uncomplicated by entering a certain text in the search field of the report filter. Now I want to reproduce this in VBA and just can’t find the solution. When I’m entering the text “_0600” in the report filter, the macro recorder traces the following:

Sub recordedMacro()    ActiveSheet.PivotTables("PivotTable4").PivotFields( _        "[Range].[Profit Center].[Profit Center]").VisibleItemsList = Array( _        "[Range].[Profit Center].&[333_0600]", _        "[Range].[Profit Center].&[444_0600]")End Sub

So I then defined the variable "pc" as a string and assigned the text to search for. But my attempt to replace the 2 found values with something like "*" & pc & "*" failed.So I made further attempts because I often read about changing the visibility of pivotitems manually, but unfortunately I wasn’t successful. Those things I tried:

Sub filterTextPart()    Dim pc As String 'text for filtering pivot    Dim pt As PivotTable    Dim pf As PivotField    Dim pi As PivotItem        Sheets(4).Activate    Set pt = ActiveSheet.PivotTables("PivotTable4")    Set pf = pt.PivotFields("[Range].[Profit Center].[Profit Center]")    pc = Sheets(1).Range("C22").Value 'e.g. "_0600"        pt.CubeFields(2).EnableMultiplePageItems = True    pf.ClearAllFilters        'attempt 1 --> runtime error 1004    'pf.PivotFilters.Add2 Type:=xlCaptionContains, Value1:=pc        'attempt 2: pf.PivotItems.Count always returns 0!    For i = 1 To pf.PivotItems.Count        If pf.PivotItems(i) Like "*" & pc & "*" Then            pf.PivotItems(i).Visible = True        Else            pf.PivotItems(i).Visible = False        End If    Next i        'attempt 3: also here no output because no items are found    With pf        For Each pi In .VisibleItems            MsgBox pi.Name        Next pi    End WithEnd Sub

I would really appreciate any ideas on this… I’m attaching an example file to make the issue more clear. I really need a solution because my original file has over 200.000 data sets and this is really slow by using normal filters without pivot tables. (I’m using Excel 2016 and Windows 10.)Many thanks in advance and best regards,evaem

[evaem]

Oh dear, all my line breaks are ignored. :o I'm sorry for that. Anybody knows how to change this?

[p45cal]

Your code with carriage returns:

Sub recordedMacro()
ActiveSheet.PivotTables("PivotTable4").PivotFields("[Range].[Profit Center].[Profit Center]").VisibleItemsList _
  = Array("[Range].[Profit Center].&[333_0600]", "[Range].[Profit Center].&[444_0600]")
End Sub


Sub filterTextPart()
Dim pc As String    'text for filtering pivot
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Sheets(4).Activate
Set pt = ActiveSheet.PivotTables("PivotTable4")
Set pf = pt.PivotFields("[Range].[Profit Center].[Profit Center]")
pc = Sheets(1).Range("C22").Value    'e.g. "_0600"
pt.CubeFields(2).EnableMultiplePageItems = True
pf.ClearAllFilters
'attempt 1 --> runtime error 1004
'pf.PivotFilters.Add2 Type:=xlCaptionContains, Value1:=pc
'attempt 2: pf.PivotItems.Count always returns 0!
For i = 1 To pf.PivotItems.Count
  If pf.PivotItems(i) Like "*" & pc & "*" Then
    pf.PivotItems(i).Visible = True
  Else
    pf.PivotItems(i).Visible = False
  End If
Next i
'attempt 3: also here no output because no items are found
With pf
  For Each pi In .VisibleItems
    MsgBox pi.Name
  Next pi
End With
End Sub

[Paul_Hossler]

I think the easist way would be to add a helper column and use that in the Page Field. Don't need VBA at all that way

Capture.JPG

[evaem]

Thanks for your help. This workaround works quite well! BR evaem