Macro to select multiple items in a pivot table

[muhleebbin]

Can someone help with the following code?

At this moment in time I don't have anything that fits var1 and var3 but they will eventually come up. Is there a way to still have this code work but then have var1 and var3 not effect the pivot until there is data for that?

Also is there a way where A1 in sheet2 doesn't need to hit enter for it to take effect? A1 is currently a data validation of the dates available

Thanks for your help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'This line stops the worksheet updating on every change, it only updates when cell
'A1 is touched
If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub
 
Dim var1 As String, var2 As String
var1 = Worksheets("Sheet1").Range("S1")
var2 = "THREE DAY PASS"
    With Worksheets("Sheet1").PivotTables("PivotTable19").PivotFields("Day(s)") '.PivotFilters
        .ClearAllFilters
        For Each Pi In .PivotItems
            Select Case Pi.Name
                Case var1, var2
                    Pi.Visible = True
                Case Else
                    Pi.Visible = False
            End Select
        Next
    End With
    
Dim var3 As String
var3 = Worksheets("Sheet1").Range("S2")
    With Worksheets("Sheet1").PivotTables("PivotTable20").PivotFields("Allocated Day") '.PivotFilters
        .ClearAllFilters
        For Each Pi In .PivotItems
            Select Case Pi.Name
                Case var3
                    Pi.Visible = True
                Case Else
                    Pi.Visible = False
            End Select
        Next
    End With
 
End Sub

 

[DanteAmor]

Re: Help with a macro to select multiple items in a pivot table

I'm glad to help you. Thanks for the feedback.