Using Code to show all pivot items
In general:
Sub ShowAllPivot()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
Next pf
Next pt
End Sub
For your specific example:
Set pt = ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship")
Set pf = pt.PivotFields("Order No")
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
HTH,
Bernie
MS Excel MVP
wrote in message
ups.com...
I have a pivot table called "Relationship" contained within the
worksheet "Relationship Table" which contains a column field called
"Order No" where individual pivot items for this column can be hidden.
I am trying to write code to automatically show all of the pivot items.
When I use the following ShowAllItems command nothing happens. Why?
ThisWorkbook.Worksheets("Relationship
tables").PivotTables("Relationship").PivotFields(" Order
No").ShowAllItems = True
I have also tried to use the following statement in an array to set
each item to visible but that causes me to get the "Run-time error
'1004' Unable to set property of the Pivot Item class" error. What is
really frustrating is that I can use this statement to set the pivot
item visible to false but not to true.
ThisWorkbook.Worksheets("Relationship
tables").PivotTables("Relationship").PivotFields(" Order
No")..PivotItems(i).Visible = True
Any help given would be greatly appreciated.
|