Dynamically select multiple pivotitems

[Trushant]

Hi,

I want to select multiple items in a pivot filter (pivot items) using the value in a specific cell. I have a pivotfield "Services Applicable" having pivotitems ("Imaging", "Cataloguing", "Imaging, Cataloguing", "Imaging Cataloguing, Fly"). I want to select multiple items basis the value in cell H1.
For eg: H1 = Imaging
In the pivotfield("services applicable") = Multiple Items (Imaging","Imaging, Cataloguing", "Imaging Cataloguing, Fly")
(It should not be just Imaging)

 

[chirayu]

Sub PVT_Change()

Dim PVT As String 'pivot
Dim PVTF As String 'filter
Dim VAL As String 'Value used for filter

PVT = "PivotTable1"
PVTF = "Service Applicable"
VAL = Range("H1")

With ActiveSheet.PivotTables(PVT).PivotFields(PVTF)

    For i = 1 To .PivotItems.Count - 1
       
        If UCase(.PivotItems(i)) Like "*" & UCase(VAL) & "*" Then
            .PivotItems(i).Visible = True
        Else
            .PivotItems(i).Visible = False
        End If
       
    Next

End With

End Sub

 

[Chihiro]

Personally, I'd just add helper column to the source to match filter grouping/category and filter based on that.

Edit: Ah, never mind. Didn't see the overlap.

 

[chirayu]

@Chihiro technically even with the overlap you could still use a vlookup using a wildcard e.g. "*" & Imaging & "*"
Then stick a TRUE/FALSE IFERROR statement on top of that

 

[Trushant]

Hi Chirayu,

The code works perfect. Thanks a lot!!Really appreciate your help

 

[Trushant]

Hi Chirayu,

There is a small issue. The code doesn't work after two to 3 iterations. It then automatically selects "All" items.

 

[Chihiro]

For PivotTable filtering code. It's always good idea to reset the filter at start (you'd need loop to make all items visible), then apply the actual filter operation.

Pivot will require at least one item to be Visible = True, or it will error out.

 

[chirayu]

Modified the code as per what chihiro said

Sub PVT_Change()

Dim PVT As String 'pivot
Dim PVTF As String 'filter
Dim VAL As String 'Value used for filter

PVT = "PivotTable1"
PVTF = "Service Applicable"
VAL = Range("H1")

With ActiveSheet.PivotTables(PVT).PivotFields(PVTF)
   
    For i = 1 To .PivotItems.Count - 1
        .PivotItems(i).Visible = True
    Next
   
    For i = 1 To .PivotItems.Count - 1
       
        If UCase(.PivotItems(i)) Like "*" & UCase(VAL) & "*" Then
            .PivotItems(i).Visible = True
        Else
            .PivotItems(i).Visible = False
        End If
       
    Next

End With

End Sub