muhleebbin
Active Member
- Joined
- Sep 30, 2017
- Messages
- 252
- Office Version
- 365
- 2019
- 2016
- 2013
- 2010
- Platform
- Windows
- MacOS
- Mobile
- 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!
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