Macro to select multiple items in a pivot table

muhleebbin

Active Member
Joined
Sep 30, 2017
Messages
252
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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!

Code:
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
 
Re: Help with a macro to select multiple items in a pivot table

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top