CODEWITHSUNDEEP
vbaexcelfieldpivot-table
Miticus
Miticus

Reputation: 1

Unable to get PivotFields property of the PivotTable class

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.

Upvotes: 0

Views: 9853

Answers (1)

Tim Williams
Tim Williams

Reputation: 166850

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

Upvotes: 1

Related Questions