Excel VBA: How to use an Array as Filter Criteria for a Pivot Table

Question

I have a report that is generated every month that shows the shortages and overages of all PO's sold that month. I am building a Macro that formats the data in a way to only show which invoices had overages on them. I've built most of the macro to work dynamically but there is only one more problem in my way. On the active sheet there is 2 Pivot Tables, PivotTable1 is filtered to only show the Invoices with Overages so I've gone ahead and wrote code to store all the values in ArrayA. Now I need PivotTable2's PivotField("Inv") to display the invoices that are in ArrayA. The Code I have now runs but its just going through each Pivot Item in that field and hiding it. It seems like there's an issue with what value is being checked when it decides whether or not to hide or show the Pivot Item or I think the way I'm incorporating the Array is incorrect. Anyways here is my Code:

Sub OverageArray()

Dim ArrayA() As Variant
Dim Counter As Integer
Dim RowCount As Long

Dim PT As PivotTable
Dim PTItm As PivotItem

Set PT = ActiveSheet.PivotTables("PivotTable2")

RowCount = (Range("A1048576").End(xlUp).Offset(-1, 0).Row - 3)

ReDim ArrayA(RowCount)

    'For Loop that Builds the Array
    For Counter = 0 To RowCount
        ArrayA(Counter) = Cells((Counter + 4), 1).Value
    Next

    'This Filters the Pivot Field Based on the Array's Values
    For Each PTItm In PT.PivotFields("Inv#").PivotItems
        If Not IsError(Application.Match(PTItm.Caption, ArrayA, 0)) Then ' check if current item is not in the filter array
            PTItm.Visible = True
        Else
            PTItm.Visible = False
        End If
    Next PTItm

End Sub

shayota shayota · Accepted Answer · 2020-03-26T22:23:59

Good news, I was able to figure it out on my way.

Turns out declaring ArrayA() as a variant saved the values as Integers and this code seemed to only work when the values were stored as strings. So all I had to do was declare ArrayA() as String instead of Variant so that it saved all the values as strings instead of Integers.

Excel VBA: How to use an Array as Filter Criteria for a Pivot Table

1 Answers