Excel VBA pivot table filtering by barcode

Question

I've been trying to create sub, that helps filter list of goods by selecting few barcodes. I know I can do it manually, but it's very long and frustrating, since there are about ~2000 unique barcodes. I've recorded a Macro and found a few similar answers here, but i get an error "Run-time error '1004': Application defined or object-defined error' it stucks when PI.value gets assigned true or false: "PI.Visible = True; Pi.Visible = False".

Code:

Private Sub CommandButton1_Click()

Dim MyNames() As Variant
Dim objPivotField As PivotField
Dim i As Long
Dim PI As PivotItem
Set objPivotField = _
ActiveSheet.PivotTables("PivotTable1").PivotFields(Index:="[Prekė].[Barkodas].[Barkodas]")
MyNames = Array("4770349225872", "4770033220077", "7622400004773")
With ActiveSheet.PivotTables("PivotTable1").PivotFields(Index:="[Prekė].[Barkodas].[Barkodas]")

    For i = LBound(MyNames) To UBound(MyNames)

    For Each PI In .PivotItems
        If PI.Name = MyNames(i) Then
        PI.Visible = True
    Else
        PI.Visible = False
    End If
    Next PI
    Next i
End With End Sub

And here's Macro that I recorded for filtering:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Prekė].[Barkodas].[Barkodas]").VisibleItemsList = Array("", _
    "[Prekė].[Barkodas].&[4750398000132]", "", "[Prekė].[Barkodas].&[4046234141238]", _
    "[Prekė].[Barkodas].&[4770248342625]")

Could you post more about the setup of the pivot table? I tried a small example with logic similar to yours and it worked, but I used With ActiveSheet.PivotTables("PivotTable1").PivotFields("Barkodas") — Dan Wagner
Also, looping through the barcodes once and checking each PivotItem against MyNames would go faster than looping through the barcodes three times, each time checking for a single barcode — Dan Wagner

Dan Wagner Dan Wagner · Accepted Answer · 2014-04-02T12:15:28

As written above in the comments, here is the method I used to filter a pivot table with VBA:

pivot table setup

Option Explicit
Sub FilterPivotTable()

Dim PT1 As PivotTable
Dim PT1Barkodas As PivotField
Dim MyNames() As Variant
Dim PivotIdx As Long

'assign table, field and array values for easy reference
Set PT1 = ActiveSheet.PivotTables("PivotTable1")
Set PT1Barkodas = PT1.PivotFields("Barkodas")
MyNames = Array("4770349225872", "4770033220077", "7622400004773")

With PT1Barkodas

    'loop through all the barcodes
    For PivotIdx = 1 To PT1Barkodas.PivotItems.Count

        'logic to check if the current barcode is in the MyNames array
        If UBound(Filter(MyNames, .PivotItems(PivotIdx))) > -1 Then
            IsInArray = True
        Else
            IsInArray = False
        End If

        'if the barcode was not in the MyNames array, hide it
        If IsInArray = False Then
            .PivotItems(PivotIdx).Visible = False
        End If

    Next PivotIdx

End With

End Sub

Running this script filtered PivotTable1 according to MyNames:

result

Excel VBA pivot table filtering by barcode

1 Answers