Link to home
Start Free TrialLog in
Avatar of CarenC
CarenC

asked on

Filter pivot table based on an array - Excel VBA

I build an array based on user selections and want to use that to filter a pivot table.  The code below shows the approach I was trying but doesn't work..  The looping is wrong because it selects the site then unselects it.  I found some articles on here using using Application.Match but I'm unfamiliar with it and couldn't get it to work based on the examples I found.

Dim arrSitesToFilter() As String
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

Call ClearAllFilters

Set pvtField = pvtTable.PivotFields("SITE_NAME")

With pvtTable
    For Each pvtItem In pvtField.PivotItems
        For x = 0 To UBound(arrSitesToFilter) - LBound(arrSitesToFilter)
            If arrSitesToFilter(x) = pvtItem.Name Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If
        Next
    Next
End With
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Is SITE_NAME a Report Filter field?  If so...

Dim arrSitesToFilter() As String
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

Call ClearAllFilters

Set pvtField = pvtTable.PivotFields("SITE_NAME")

With pvtTable
    For Each pvtItem In pvtField.PivotItems
        For x = 0 To UBound(arrSitesToFilter) - LBound(arrSitesToFilter)
            If arrSitesToFilter(x) = pvtItem.Name Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If
        Next
    Next
    .PivotFields("SITE_NAME").EnableMultiplePageItems = True
End With

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial