Filter pivot table 1004 error

Question

Holla! I am trying to filter pivot table using macros. The code as follows:

Dim Pi As PivotItem
With ActiveWorkbook.Worksheets("TimeToFreq").PivotTables("timetofreq").PivotFields("Transaction status")
    .ClearAllFilters
    .PivotItems("success").Visible = True       
End With

However i am getting error 1004

Unable to get the PivotFields property of the PivotTable class

Screenshots:

I've browsed a lot of forums and the only answer i could found was that pivot table or field with such name does not exist, but mine does. I even tried to use other fields that i have and it still gives me this error. I use MS office 2013

Answer 1

Error 1004 is achieved because you either do not have PivotFields("Transaction status") or it does not have PivotItems("success").

Whenever you are working with PivotTables, PivotFields and PivotItems in VBA it is really useful to declare them as such. If you do so, you get access to them as collections and you can iterate through the objects of the collection.

In your case, something like this will allow you to manipulate the .Visible property:

Public Sub TestMe()

    Dim pi As PivotItem
    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = ActiveWorkbook.Worksheets("TimeToFreq").PivotTables("timetofreq")
    Set pf = pt.PivotFields("Transaction status")

    pt.ClearAllFilters

    For Each pi In pf.PivotItems
        Debug.Print pi.Name

        If pi.Name = "success" Then
            pi.Visible = True
        End If
    Next pi

End Sub

Edit: This is how to loop through the pivot fields

Set pt = ActiveWorkbook.Worksheets("TimeToFreq").PivotTables("timetofreq")
For Each pf In pt.PivotFields
    Debug.Print ">" & pf.Name & "<"
Next pf

Edit 2, after screenshot from OP:

Replace:

PivotFields("Transaction status")

With:

PivotFields("[Data_New].[Transaction status].[Transaction status]")

Answer 2

The quickest way to filter a field on one item is to make it a PageField, and use code like Deepak has posted above. Otherwise, you need to use code such as I have posted at Pivotfields multiple filter that ensures that at least one item will remain visible at all times.

Sub FilterPivot()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Long
Dim vItem As Variant
Dim vItems As Variant

Set pt = ActiveSheet.PivotTables("SomePivotTableName") 'Replace with your PivotTable name 
Set pf = pt.PivotFields("SomePivotTableName") 'Replace with your PivotField name 

vItems = Array("This", "That", "SomethingElse") 'Your terms here
' If filtering on just one item, use vItems = Array("This")

pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed

With pf

    'At least one item must remain visible in the PivotTable at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible        
    .PivotItems(1).Visible = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .PivotItems.Count
        If .PivotItems(i).Visible Then .PivotItems(i).Visible = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vItems
        .PivotItems(vItem).Visible = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the items of interest
    On Error Resume Next
    If InStr(UCase(Join(vItems, "|")), UCase(.PivotItems(1))) = 0 Then .PivotItems(1).Visible = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Pivot, so I have cleared the filter"
    End If
    On Error GoTo 0

End With

pt.ManualUpdate = False

End Sub