The ShowAllItems property is like adding a check mark to the 'Show items
with no data' option. It's not the equivalent of checking (Show All) in
the item dropdown.

wrote:
Thanks Debra

Setting the Sort to Manual fixed the problem with setting pivot item
visible to true. It didn't fix the ShowAllItem command which appears to
be the quickest way to do this but I can definitely use what you gave
me. If you have any insight into the later issue that would be great,
if not that's great too.

Regards
Will

Debra Dalgleish wrote:

To prevent the error, set the Sort for the field to Manual. You can do
this in the code, for example:

'==================================
Sub PivotShowItemResetSort()
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
'returns sort order to previous setting
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim intASO As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.VisibleFields
intASO = pf.AutoSortOrder
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible < True Then
pi.Visible = True
End If
Next pi
pf.AutoSort intASO, pf.SourceName
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'================================



wrote:

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
egroups.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").PivotFie lds("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").PivotFie lds("Order
No")..PivotItems(i).Visible = True

Any help given would be greatly appreciated.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html