Change Pivot filter Value to exclude all others

[MarkCBB]

Hello VBA Pros,

I am trying to edit the below code so that if the filter Items do not = Month_Name and are not selected. i.e. If Month_name = March then March must be the only Month visible so I need to add <>Month_name then .visible = false, I havnt been able to figure out the correct way to writing this.

Dim Month_Name As Range
Set Month_Name = Range("C7")
    With Sheets("List").PivotTables("PivotTable2").PivotFields("MONTH")
        .PivotItems(Month_Name.Value).Visible = True
    End With
    End With

 

[RoryA]

Dim Month_Name As Range
Set Month_Name = Range("C7")
    With Sheets("List").PivotTables("PivotTable2").PivotFields("MONTH")
        .PivotItems(Month_Name.Value).Visible = True
        For each pi in .pivotitems
           If pi.name <> month_name.value then pi.visible = false
         Next pi
    End With

If you have 2007 or later you can just apply a filter.

 

[MarkCBB]

Hey Rory,

I must say this code has been so helpfull, thank you for the help, I have updated the code as well

Sub FilterRegionalPT()
Dim Region_Name As Range
Dim i As Integer
Dim PT_Counta As Integer
pt_count = ActiveSheet.PivotTables.Count
Set Region_Name = Range("B2")
i = 0
 
Application.ScreenUpdating = False

Do
    On Error Resume Next
        i = i + 1
        With Sheets(11).PivotTables(i).PivotFields("REGION")
            .PivotItems(Region_Name.Value).Visible = True
             For Each Pi In .PivotItems
                If Pi.Name <> Region_Name.Value Then Pi.Visible = False
             Next Pi
        End With
    On Error GoTo 0
Loop Until i = pt_count
Application.ScreenUpdating = True
End Sub