VBA Help - Filter a Pivot table based on list of values

[largeselection]

Hi All -

I've searched around and haven't found a great solution for this, I found some other older posts, but the solutions posted didn't seem to work for me, my guess is that the reason it didn't work is bc the pivot table I am trying to manipulate is based on powerpivot data. I suspect it is largely a formatting issue so hoping this is an easy ask! Thanks in advance!

Sub FilterPT()
Dim PI As PivotItem
    
    With Worksheets("PT").PivotTables("ItemPT").PivotFields("[Prod].[InvID].[InvID]")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(ActiveSheet.Range("B5:B500"), PI.Value) > 0
        Next PI
    End With
End Sub

This does successfully clear the filters for that pivot for the field "[Prod].[InvID].[InvID]", but then fails when trying to do the filter.

Essentially what I want to do is filter that field based on a list of IDs that are in the range B5:B500 - to add a little complexity that range can change in length (could be as few as 1 or as many of a few hundred) and there is a rare possibility that someone could enter an ID that doesn't exist in the PT data (which I was planning to skip with onerror next.

Alternatively,
I tried recording to better understand how the filter works and what the fields look like and I got

Sub Macro1()
    ActiveSheet.PivotTables("ItemPT").PivotFields( _
        "[Prod].[InvID].[InvID]" _
        ).VisibleItemsList = Array( _
        "[Prod].[InvID].&[ProdMAK1]", _
        "[Prod].[InvID].&[ProdMAK2]", _
        "[Prod].[InvID].&[ProdMAK3]")
end sub

So another approach could be to somehow build the array in this dynamically based on the list of IDs. Currently in B5:B7 it just lists out "ProdMAK1", "ProdMAK2", "ProdMAK3" so something that would check each cell in the range and if there is something there then add it to the array with "[Prod].[InvID].&[............]" wrapped around each entry?

Thanks for your help! Always so excited to learn from this community.

 

[largeselection]

Quick update - I've tried the path of creating an array and now I can create an array that when I print it appears to contain the things I want, but when I try to use it as the array for the pivot it doesn't work. Any thoughts?

Sub Macro1()
Dim myarray() As Variant
Dim datarange As Range, cell As Range
Dim x As Long

Set datarange = ActiveSheet.Range("A2:A3")
ReDim myarray(datarange.Cells.Count)

For Each cell In datarange.Cells
    myarray(x) = cell.Value
    x = x + 1
Next cell

    ActiveSheet.PivotTables("ItemPT").PivotFields( _
        "[Prod].[InvID].[InvID]" _
        ).VisibleItemsList = Array(myarray())
End Sub

If I change the last line to myarray(x) then there isn't an error message, but it also doesn't update the pivot table to the IDs that are listed.

 

[largeselection]

I see a bunch of people have viewed this...I've continued trying different approaches with no success. If I need to I can probably just convert this into a regular data/pivot table vs powerpivot, would that help simplify this? Only reason why I didn't initially do this was bc then the workbook size was pretty large...