Pivot Table Show All

[icu222much]

How would I reset a pivot table to that it shows all of the items without using loops? Currently, I am using a loop to go through each list and select all the items. This however takes an extremely long time. I have tried using .showAllItems = True but it doesn't seem to work. Any advice?

[geekgirlau]

You're right, ShowAllItems = useless! I believe 2007 has a ClearAllFilters option for the pivot field, but for 2003 try this

[VBA]
Dim pvt As PivotTable
Dim pvtFld As PivotField
Dim pvtItm As PivotItem

Application.Calculation = xlCalculationManual
On Error Resume Next

Set pvt = ActiveSheet.PivotTables("pvtControlBox")
pvt.ManualUpdate = True

For Each pvtFld In pvt.PivotFields
' doesn't work!!!
pvtFld.ShowAllItems = True

For Each pvtItm In pvtFld.PivotItems
pvtItm.Visible = True
Next pvtItm
Next pvtFld

pvt.ManualUpdate = False
Application.Calculation = xlCalculationAutomatic
[/VBA]

[icu222much]

Thank you for your reply GeekGirlAu. I ran your code but it still look a very long time to run as I have lots of items in a few of my fields. I think the issue is becuase it is a loop within a loop. Is there a way where I can just select the "Show All" item instead of going through each item and manually selecting it?

Oh yes, and I am running Office 2003.

[geekgirlau]

This is the only method I could find. Setting ManualUpdate to True improved the performance somewhat, but only to a degree.

[icu222much]

I tested my code with and without manual update. They both took ~3 min give or take a second.

Thank you though