PivotTable Problem

Here are two macros I RECORDED to collapse and expand the data viewed in a
pivot table.

Sub CollapseView()

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
.PivotItems("C").Visible = False
.PivotItems("PA").Visible = False
.PivotItems("PE").Visible = False
.PivotItems("PU").Visible = False
.PivotItems("PX").Visible = False
End With
End Sub

Sub ExpandView()

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
.PivotItems("C").Visible = True
.PivotItems("PA").Visible = True
.PivotItems("PE").Visible = True
.PivotItems("PU").Visible = True
.PivotItems("PX").Visible = True
End With
End Sub

Sub CollapseView works just fine. When I try to run ExpandView I get Error
Message 1004 - " Unable to set the Visible property of the PivotItem class."
I am not able to change any of the PivotItems with this code.

I do not understand why I can programtically set the visible property to
false but can't set it to true. This is the first time I have not been able
to run code that I recorded. Any ideas on what is happening here would be
much appreciated.
--
Stan Shoemaker
Palo Alto, CA

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
'================================


stanshoe wrote:
> Here are two macros I RECORDED to collapse and expand the data viewed in a
> pivot table.
>
> Sub CollapseView()
>
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
> .PivotItems("C").Visible = False
> .PivotItems("PA").Visible = False
> .PivotItems("PE").Visible = False
> .PivotItems("PU").Visible = False
> .PivotItems("PX").Visible = False
> End With
> End Sub
>
> Sub ExpandView()
>
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
> .PivotItems("C").Visible = True
> .PivotItems("PA").Visible = True
> .PivotItems("PE").Visible = True
> .PivotItems("PU").Visible = True
> .PivotItems("PX").Visible = True
> End With
> End Sub
>
> Sub CollapseView works just fine. When I try to run ExpandView I get Error
> Message 1004 - " Unable to set the Visible property of the PivotItem class."
> I am not able to change any of the PivotItems with this code.
>
> I do not understand why I can programtically set the visible property to
> false but can't set it to true. This is the first time I have not been able
> to run code that I recorded. Any ideas on what is happening here would be
> much appreciated.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra-

Thank you. I really appreciate your help.

Stan Shoemaker
Palo Alto, CA

"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
> '================================
>
>
> stanshoe wrote:
> > Here are two macros I RECORDED to collapse and expand the data viewed in a
> > pivot table.
> >
> > Sub CollapseView()
> >
> > With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
> > .PivotItems("C").Visible = False
> > .PivotItems("PA").Visible = False
> > .PivotItems("PE").Visible = False
> > .PivotItems("PU").Visible = False
> > .PivotItems("PX").Visible = False
> > End With
> > End Sub
> >
> > Sub ExpandView()
> >
> > With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
> > .PivotItems("C").Visible = True
> > .PivotItems("PA").Visible = True
> > .PivotItems("PE").Visible = True
> > .PivotItems("PU").Visible = True
> > .PivotItems("PX").Visible = True
> > End With
> > End Sub
> >
> > Sub CollapseView works just fine. When I try to run ExpandView I get Error
> > Message 1004 - " Unable to set the Visible property of the PivotItem class."
> > I am not able to change any of the PivotItems with this code.
> >
> > I do not understand why I can programtically set the visible property to
> > false but can't set it to true. This is the first time I have not been able
> > to run code that I recorded. Any ideas on what is happening here would be
> > much appreciated.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>