VBA for Pivot Table Report Filter

[thelostscott]

Hi all,

Is there another way to write some code that will allow me to "Unselect" every option in a Report Filter for a Pivot Table, then "Select" 3 options only?

I have tried something like this to no avail:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Business").CurrentPage = "(All)"

ActiveSheet.PivotTables("PivotTable1").PivotFields("Business").EnableMultiplePageItems = True
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business")
        .PivotItems("(ALL)").Visible = False
        .PivotItems("Business 1").Visible = True
        .PivotItems("Business 2").Visible = True
        .PivotItems("Business 3").Visible = True
    End With

I have tried recording a macro but it generates a massive list of every option available =False, but this list will change every time I want to run this code and I don't it to keep pulling up errors just because some of the list has changed!

Thanks in advance

 

[Misca]

Try:

Dim PT As PivotTable
Dim i As Integer
Set PT = ActiveSheet.PivotTables(1)
    
    With PT.PivotFields("Business")
       ' .CurrentPage = "(All)"
        .EnableMultiplePageItems = True
        
        For i = 1 To .PivotItems.Count
            With .PivotItems(i)
                Select Case .Name
                
                Case "Business 1", "Business 2", "Business 3"
                    .Visible = True
                Case Else
                    .Visible = False
                End Select
            End With
        Next i
    End With