Unable to get PivotFields property of the PivotTable class

Question

I'm trying to make a macro that changes the filters of several PivotTables (not all), but i'm getting an error on the PivotFields, here's a sample of my code:

Sheets("Sheet1").PivotTables("PivotTable" & pivot_counter).PivotFields( _
"[year].[year].[year]").VisibleItemList = Array("")

My questions are:

1- Why do we use PivotFields("[year].[year].[year]") when using VisibleItemList? Why do we have to repeat it and what's the meaning of it, couldn't anything about it anywhere.

2- What is supposedly wrong with this code? My pivot table has a field called "year" and the filter is set for a specific year (let's say 2013) and i wanted it change for all possible years.

Is this recorded code? If not, when you record code for this step does it look like your example code? — Tim Williams
When i record code for this step i get this code: ActiveSheet.PivotTables("PivotTable1").PivotFields("year"). _ CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable1").PivotFields("year") .PivotItems("").Visible = True .PivotItems("1900").Visible = True .PivotItems("2009").Visible = True .PivotItems("2010").Visible = True .PivotItems("2011").Visible = True .PivotItems("2012").Visible = True .PivotItems("2013").Visible = True .PivotItems("2014").Visible = True End With — Miticus
The thing is, this code from the record macro shows all the possible solutions for results for that filter. And i want to do that automatically without enumerating them. Because, i will be doing this for other several filters in my pivot tables and these fields can change with time (for example, instead of going from 2009 to 2014, it can go from 2007 to 2019). — Miticus

Tim Williams Tim Williams · Accepted Answer · 2013-08-23T18:04:08

Sub Tester()
    ShowAll ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
End Sub


Sub ShowAll(pf As PivotField)
    Dim pi As PivotItem
    Application.ScreenUpdating = False
    For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi
    Application.ScreenUpdating = True
End Sub

Unable to get PivotFields property of the PivotTable class

1 Answers