Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to select multiple items in Excel PIVOT with vba code

Copper Contributor

How to select multiple ITEMS in Excel PIVOT with vba code.

I want to select store S846 and SG07 in PIVOT. Please suggest

 

VBA code:

Range("B2").Select

    ActiveSheet.PivotTables("PivotTable3").PivotFields("Store").CurrentPage = _

        "(All)"

        Application.ScreenUpdating = False

    ActiveSheet.Range("B2") = "S846"

Application.ScreenUpdating = True

1 Reply

@amol bhosale Try something along these lines...

 

Sub SelectMultipleItems()
    Dim Item As PivotItem
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Store")
        .ClearAllFilters
        For Each Item In .PivotItems
            Select Case Item.Name
                Case "S846", "SG07"
                    Item.Visible = True
                Case Else
                    Item.Visible = False
            End Select
        Next Item
        .EnableMultiplePageItems = True
    End With
End Sub