PDA

View Full Version : Pivot report filter with wildcard



evaem
08-27-2019, 12:44 AM
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 SubSo 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 SubI 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
08-27-2019, 12:46 AM
Oh dear, all my line breaks are ignored. :o I'm sorry for that. Anybody knows how to change this?

p45cal
08-27-2019, 01:57 AM
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
08-27-2019, 05:27 AM
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

24879

evaem
08-28-2019, 03:54 AM
Thanks for your help. This workaround works quite well! :)BR evaem