Hi,

I'm just looking at pivot table stuff via VBA for the
first time and am having some hassles:

..PivotItems(x).Visible = True

is returning a 'run time error 1004 app/object defined
error' for PivotItems in a given collection (when the
property is initially manually set to False prior to
running), whereas the same property set to false via
script, on the same item (after manually setting to True)
works fine. I can't work out what is going wrong... I'm
using XL2000. Any ideas??

Debrah Dalgleish had written about this:

http://groups.google.com/groups?thre...ontextures.com
To prevent the error, set the Sort for the field to Manual. You can do
this in the code, for example:

Sub PivotShowItemAllField()
'For version 2000 -- show all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Salesman")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Salesman")
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 xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy

"Marshall" wrote in message
...
Hi,

I'm just looking at pivot table stuff via VBA for the
first time and am having some hassles:

.PivotItems(x).Visible = True

is returning a 'run time error 1004 app/object defined
error' for PivotItems in a given collection (when the
property is initially manually set to False prior to
running), whereas the same property set to false via
script, on the same item (after manually setting to True)
works fine. I can't work out what is going wrong... I'm
using XL2000. Any ideas??

Thanks Tom, much appreciated. Will check this out.

-----Original Message-----
Debrah Dalgleish had written about this:

http://groups.google.com/groups?threadm=3EFB17B7.90707%
40contextures.com
To prevent the error, set the Sort for the field to
Manual. You can do
this in the code, for example:

Sub PivotShowItemAllField()
'For version 2000 -- show all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Salesman")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Salesman")
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 xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy

"Marshall" wrote
in message
...
Hi,

I'm just looking at pivot table stuff via VBA for the
first time and am having some hassles:

.PivotItems(x).Visible = True

is returning a 'run time error 1004 app/object defined
error' for PivotItems in a given collection (when the
property is initially manually set to False prior to
running), whereas the same property set to false via
script, on the same item (after manually setting to
True)
works fine. I can't work out what is going wrong...
I'm
using XL2000. Any ideas??






.