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
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