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

In the pivot table at least one record you must select.
The following checks if there is no selection, then sends you a msg.

    Dim var1 As String, var2 As String
    Dim n As Long
    
    var1 = Worksheets("Sheet1").Range("S1")
    var2 = "THREE DAY PASS"
    n = 0
    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
                    n = n + 1
                    If n < .PivotItems.Count Then
                        Pi.Visible = False
                    Else
                        MsgBox "No match"
                        .ClearAllFilters
                    End If
            End Select
        Next
    End With

 

[muhleebbin]

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

Hey Dante

Thanks for the input, I guess I should clarify what this is going to be used for and why I asked for the method where var1 and var3 could be temporarily unavailable.

This pivot selection helps populate attendees for an event. At the moment only "Three Day Passes" have been issued but eventually Friday, Saturday and Sunday only passes will be issued. Var1 will be those options eventually. On sheet2 cell a1 is a data validation to select which report to view: "Friday, Saturday or Sunday" attendees.

I'm hoping to be able to still select from that cell which report to view while there may not be data yet for a particular day. Right now if there's no data for let's say Sunday, selecting Sunday doesn't update the pivot properly.

Hope that makes more sense.

 

[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

 

[muhleebbin]

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

I'm not having an issue with matching data type (mine are just words ie THREE DAY PASS, Friday Only etc.). It's more if there is no data for a specific date i.e. Sunday only. When I go to select the Friday Report but then select Sunday, if there's no Sunday Only passes then the report doesn't fill properly. It keeps the previous day's filter, in this case Friday.

I'm trying to make it where var1 (date specific filter) isn't necessary if there's no data for it.

 

[DanteAmor]

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

If there is no data to filter then it does not filter anything.

 

[muhleebbin]

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

There will eventually be data for that as stated before....."At the moment only "Three Day Passes" have been issued but eventually Friday, Saturday and Sunday only passes will be issued"

Which is why i'm looking for a solution to this

 

[DanteAmor]

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

Did you do the simple test?

 

[muhleebbin]

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

Simple test? Sorry not sure what you're referring to there

Testing i've done so far includes all 4 entries which runs normally. However if I only include 3 entries: "Three Day Pass, Friday Only, Saturday Only" to see what happens when I select Sunday. In that instance, Sunday brings up the previous day selected report. Even further testing would be switching Friday Only to Sunday Only (let's say someone made an input error) that runs into a debug issue where it's looking for var1.

Basically i'm trying to dummy proof this report so that it can pull the correct data set for an attendee report for any given day.

 

[muhleebbin]

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

Actually I think I know where to go with this....

I'll test when I get back to the office