Error:Unable to set the visible property of the PivotItem class.

[Guest]

I got the error
Unable to set the visible property of the PivotItem class.

when i try to set a pivotitem to visible:
Dim PF As PivotField
Dim PFI As PivotItem
For Each PF In PFs
Set PFs = ActiveSheet.PivotTables("PivotTable2").PivotFields
For Each PFI In PF.HiddenItems
PFI.Visible = True
Next PFI
Next PF

is there any solution, I have found yet no such (only at
www.experts-exchange.com/msoffice/Q_20275761.html
, but they wanted me to register first...)

Thanks
Holger

 

[Tom Ogilvy]

See Debra Dalgleish's site

http://www.contextures.com/xlPivot03.html

Note that in her sample code she sets the autosort to manula before
unhidding the item, so this must be some bug she is allowing for:

Sub PivotShowItemAllVisible()
'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
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.AutoSort xlAscending, pf.SourceName
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
-- Regards,Tom Ogilvy

 

[Guest]

Hi Tom,

the example works fine. I am now trying to get my stuff running.

Thanks a lot.
Holger