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

The macro works for me, if var1 or var2 have data, perform the appropriate filter, check the data of var1 matches in letters and spaces with the elements of the table, if it is a number, check that the elements are also numbers, if it is a date, check that the elements are also date and have the same date format.
It is complicated to work with numbers and dates, perform a simple test with a letter, put in var1 = "a", in your data put some letters "a". Do the test

This test

 

[muhleebbin]

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

Ahh that....yeah it was fine....it's a matter if you know you'll have A, B, C and D eventually but only currently have A, B and C. The report for D runs into a debug issue.

I thought I could default the Else to automatically just select A but again the issue falls on the "Case var1, var2" line. If there's no var1 (D) yet then it does't know what to do with that. D will eventually be added, it's just not there right now.

 

[muhleebbin]

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

Is there a way to make var1 non essential and part of an if statement?

Something to the effect....

If var1 is nothing
var2 pi.visible = true
Else
var1 > 0
var1, var2 pi.visible true

 

[DanteAmor]

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

You could explain it with real data, I'll gladly review it

 

[muhleebbin]

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

Right now the only data that I have is...

[TABLE="width: 446"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Pass #[/TD]
[TD]Attendee[/TD]
[TD]Day(s)[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]THREE DAY PASS[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD]THREE DAY PASS[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]C[/TD]
[TD]THREE DAY PASS[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]D[/TD]
[TD]THREE DAY PASS[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]E[/TD]
[TD]THREE DAY PASS[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]F[/TD]
[TD]THREE DAY PASS[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Test 1[/TD]
[TD]FRIDAY ONLY[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Test 2[/TD]
[TD]SATURDAY ONLY[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Test 3[/TD]
[TD]SATURDAY ONLY[/TD]
[/TR]
</tbody>[/TABLE]


If you switch Test 3 to SUNDAY ONLY then the report works for all 3 days that i'd like to produce the report. However if switched to this, the Sunday report runs into an issue as var1 is no longer an option

 

[DanteAmor]

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

var1 goes with the Attendee column and var2 goes with the Day(s) column?

 

[muhleebbin]

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

In this case var1 goes with the day specific options and var2 is set to "THREE DAY PASS", hence the multiple items filter in the pivot.

Basically I want the report to produce the names and pass numbers of the people attending each day. If I select Friday, I want the report to show the attendees that can be there Friday (abcdef and test1). But knowing that eventually there will be people getting Sunday only passes. Since Sunday isn't issued yet, that report runs into debug issues since var1 is nothing.

var1 is truly a variable whereas var2 is always a selected filter.

Which is why I asked if var1 could be a conditional variable based on its existence or not.

 

[DanteAmor]

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

I attach my test file, in column B are the days, in cell S1 is the day for var1.
You could play with the data in column B and with the data in cell S1 and tell me which of the cases you are testing have a problem.
I did several tests and no case I have "debug issues"

https://www.dropbox.com/s/wksvv5p4vgf5jre/filter.xlsm?dl=0

 

[muhleebbin]

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

That is so weird! Of course yours is working perfectly and now I can see why you think i'm a retard!

 

[muhleebbin]

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

I somehow fixed mine....not sure if it was by resetting the data source or if breaking a bad link between the two workbooks fixed it.

Anyhow, your solution works beautifully now in mine and thank you very much for being patient with me and helping me resolve this issue!