Thread: Handling pivot tables with macros - Filtering fields

Hello!
How can I filter items from a certain field of a Pivot Table without having to use something like

With ActiveSheet.PivotTables("Table1").PivotFields("Field1")
        .PivotItems("Item1").Visible = False
        .PivotItems("Item2").Visible = False
        .PivotItems("Item3").Visible = False
        .PivotItems("Item4").Visible = False
        .PivotItems("Item5").Visible = False
                        .
                        .
                        .
                        .
                        .
                        .
        .PivotItems("ItemN").Visible = False
End With

where every item has to be turned off one by one? Is it possible to hide them all and then use visible.true to show only the item I want to see? Keep in mind that the items names aren't as simple as shown in the code, so using a changing string to call each name inside a loop isn't possible.
Thanks in advance

Okay, this is the code I came up with.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean)
    'Filters pivot tables associated to the item you click on
    Dim a As Integer 'Columns number
    Dim b As Long 'Rows number
    Dim x As Byte 'For knowing what stage of the filter is running
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim strPF As String 'Name of the pivot field to be filtered
    Dim strPI As String 'Only item to stay visible
    
    Application.ScreenUpdating = False
    
    If Sh.Name = "Clientes" Then
        strPF = "Razón social"
    ElseIf Sh.Name = "RRCC" Then
        strPF = "Representante comercial"
    ElseIf Sh.Name = "Productos" Then
        strPF = "Ejercicio/Período"
    Else
        Exit Sub
    End If
    
    If Target.Column = 1 And Target.Row <= Range("A1").CurrentRegion.Rows.Count Then
        a = Range("A1").CurrentRegion.Columns.Count
        b = Range("A1").CurrentRegion.Rows.Count
        If Target.Row <= 2 Then
            x = 1
                       
            If x = 1 Then
                Sheets("TD MUsMP").Select
            End If
DontFilterPT:
            Set pt = ActiveSheet.PivotTables(1)
            Application.DisplayAlerts = False
            On Error Resume Next
            With pf
                .AutoSort xlManual, .SourceName
                For Each pi In pf.PivotItems
                    pi.Visible = True
                Next pi
                .AutoSort xlAscending, .SourceName
            End With
            Application.DisplayAlerts = True
            x = x + 1
            If x = 2 Then
                Sheets("TD CMN").Select
                GoTo DontFilterPT
            End If
            On Error GoTo 0
        Else
            x = 1
            strPI = Target.Value            
            If x = 1 Then
                Sheets("TD MUsMP").Select
            End If
filterPT:
            Set pt = ActiveSheet.PivotTables(1)
            Set pf = pt.PivotFields(strPF)
            Application.DisplayAlerts = False
            On Error Resume Next
            With pf
                .AutoSort xlManual, .SourceName
                For Each pi In pf.PivotItems
                    pi.Visible = False
                    If pi.Value = strPI Then
                        pi.Visible = True
                    End If
                Next pi
                .AutoSort xlAscending, .SourceName
            End With
            Application.DisplayAlerts = True
            x = x + 1
            If x = 2 Then
                Sheets("TD CMN").Select
                GoTo filterPT
            End If
            On Error GoTo 0
        End If
        Cancel = True
    End If
    
    Sheets(Sh.Name).Select
    Application.ScreenUpdating = True
    
End Sub

It works perfectly.