PivotField select all

I would like to show all items in PivotFields("PERIOD_NUMBER"). Following is
my code:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Pivot2").PivotTables("pvtWageRptCGFS")
Set pf = pt.PivotFields("PERIOD_NUMBER")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

Problem: I get run time error '1004' Unable to set the Visible Property of
the Pivot Item Class on "pi.Visible = True"

Thanks in advance for any help!

CinqueTerra wrote:
> I would like to show all items in PivotFields("PERIOD_NUMBER"). Following is
> my code:
>
> Dim pt As PivotTable
> Dim pf As PivotField
> Dim pi As PivotItem
>
> Set pt = Sheets("Pivot2").PivotTables("pvtWageRptCGFS")
> Set pf = pt.PivotFields("PERIOD_NUMBER")
>
> For Each pi In pf.PivotItems
> pi.Visible = True
> Next pi
>
> Problem: I get run time error '1004' Unable to set the Visible Property of
> the Pivot Item Class on "pi.Visible = True"
>
> Thanks in advance for any help!

One cause is having an autosort designated in Field Settings,
Advanced. Autosort must be manual before the Visible property can be
set. Something like

pf.AutoSort xlManual, "PERIOD_NUMBER"

before the for loop is entered.

Tom

Awesome! Thanks :-)

"tlaw37@us.identify.com" wrote:

> CinqueTerra wrote:
> > I would like to show all items in PivotFields("PERIOD_NUMBER"). Following is
> > my code:
> >
> > Dim pt As PivotTable
> > Dim pf As PivotField
> > Dim pi As PivotItem
> >
> > Set pt = Sheets("Pivot2").PivotTables("pvtWageRptCGFS")
> > Set pf = pt.PivotFields("PERIOD_NUMBER")
> >
> > For Each pi In pf.PivotItems
> > pi.Visible = True
> > Next pi
> >
> > Problem: I get run time error '1004' Unable to set the Visible Property of
> > the Pivot Item Class on "pi.Visible = True"
> >
> > Thanks in advance for any help!
>
> One cause is having an autosort designated in Field Settings,
> Advanced. Autosort must be manual before the Visible property can be
> set. Something like
>
> pf.AutoSort xlManual, "PERIOD_NUMBER"
>
> before the for loop is entered.
>
> Tom
>
>

This is exactly what I needed too!!!! Thanks a million.

"tlaw37@us.identify.com" wrote:

> CinqueTerra wrote:
> > I would like to show all items in PivotFields("PERIOD_NUMBER"). Following is
> > my code:
> >
> > Dim pt As PivotTable
> > Dim pf As PivotField
> > Dim pi As PivotItem
> >
> > Set pt = Sheets("Pivot2").PivotTables("pvtWageRptCGFS")
> > Set pf = pt.PivotFields("PERIOD_NUMBER")
> >
> > For Each pi In pf.PivotItems
> > pi.Visible = True
> > Next pi
> >
> > Problem: I get run time error '1004' Unable to set the Visible Property of
> > the Pivot Item Class on "pi.Visible = True"
> >
> > Thanks in advance for any help!
>
> One cause is having an autosort designated in Field Settings,
> Advanced. Autosort must be manual before the Visible property can be
> set. Something like
>
> pf.AutoSort xlManual, "PERIOD_NUMBER"
>
> before the for loop is entered.
>
> Tom
>
>