PivotFields property

[FrancescoAFR]

Hi everyone,

I have a problem with this code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim ws As Worksheet
    Dim pv As PivotTable
    Dim pf As PivotField

    Set ws = ThisWorkbook.Worksheets("Atleti e Media")
    Set pv = ws.PivotTables("PerATleta")
    Set pf = pv.PivotFields("Media Ore Settimanale")
    
    For Each pf In pv.PivotFields
    
    pf.ClearAllFilters
    pf.PivotItems("00:00:00").Visible = False

End Sub

To try and identify the problem, I checked through the Locals to ensure the pivot table is assigned correctly and I also tried using a

For Each pf In pv.PivotFields

before setting pf, but the error message remains the same:
"Unable to get the PivotFields property of the PivotTable class"
Thanks

 

[jkpieterse]

Are you sure the pivot table has a field called "Media Ore Settimanale" exactly? Check for leading or trailing spaces and for non-printing characters like chr(160) (the non-breaking space)

 

[FrancescoAFR]

I changed the name of the field to "Sum of Media Ore Settimanale" and now it works, but the code stops at the next instruction. How can I remove any existing filters and then set one to hide all times with the value "00:00:00"?
Following the current version of the macro:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim ws As Worksheet
    Dim pv As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    Set ws = ThisWorkbook.Worksheets("Atleti e Media")
    Set pv = ws.PivotTables("PerAtleta")
    Set pf = pv.PivotFields("Sum of Media Ore Settimanale")

    pf.ClearAllFilters
    pf.PivotItems("00:00:00").Visible = False

End Sub