Will,

Personally, I would copy the data table over to a new workbook and re-create the PT - very easy to
do, the beauty of PTs. And then see if the code works or not. Often, trying out code on a workbook
can cause corruption.

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
Thanks Bernie

I tried your code but the pi.Visible = True statement still gives me
the "Run-time '1004', Unable to set the Visible property of the
PivotItem class". Still not sure why but thanks for the help.

If there are any other suggestions I would be more then happy to give
them a try.

Cheers
Will

Bernie Deitrick wrote:
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.