Pivot Table Hide Levels in VBA

I was wondering how you'd hide levels in a Pivot Table using VBA. e.g. the
way it's currently done is you right click the level and click on "Hide
Levels" on the pivot table, how can I reproduce that functionality in VBA?

thanks.

Hi Brett
Set the visible property of the item to false. If you place this code in a
loop just remember to leave at least one item visible to avoid an error.

ActiveSheet.PivotTables(1).PivotFields(2).PivotItems(2).Visible = False

Regards
Mike

"Brett" wrote:

> I was wondering how you'd hide levels in a Pivot Table using VBA. e.g. the
> way it's currently done is you right click the level and click on "Hide
> Levels" on the pivot table, how can I reproduce that functionality in VBA?
>
> thanks.
>

Hi Mike

Thanks. But that doesn't work. I get an error, "Unable to set Visible
property of the PivotItem class".

What I want it to display is as follows:
Say now my pivot table is expanded as follows:
[LEVEL 1] [LEVEL 2] [LEVEL 3] [LEVEL 4]

I want it to collapse the first two 'columns' so it displays as follows:
[LEVEL 3] [LEVEL 4]

Therefor 'hiding' the first two levels.

Regards,
Brett




"MIKE215" wrote:

> Hi Brett
> Set the visible property of the item to false. If you place this code in a
> loop just remember to leave at least one item visible to avoid an error.
>
> ActiveSheet.PivotTables(1).PivotFields(2).PivotItems(2).Visible = False
>
> Regards
> Mike
>
> "Brett" wrote:
>
> > I was wondering how you'd hide levels in a Pivot Table using VBA. e.g. the
> > way it's currently done is you right click the level and click on "Hide
> > Levels" on the pivot table, how can I reproduce that functionality in VBA?
> >
> > thanks.
> >

Hi Brett

Try this then
With ActiveSheet.PivotTables(1)
For i = 1 To 2
.PivotFields(i).Orientation = xlHidden
Next i
End With
This moves fields 1 and 2 off the table completely.

or the other way to do it is to set fields 3 and 4 to be the rows
With ActiveSheet.PivotTables(1)
For i = 3 To 4
.PivotFields(i).Orientation = xlrow
Next i
End With

Both ways get you to the same place.

Mike

"Brett" wrote:

> Hi Mike
>
> Thanks. But that doesn't work. I get an error, "Unable to set Visible
> property of the PivotItem class".
>
> What I want it to display is as follows:
> Say now my pivot table is expanded as follows:
> [LEVEL 1] [LEVEL 2] [LEVEL 3] [LEVEL 4]
>
> I want it to collapse the first two 'columns' so it displays as follows:
> [LEVEL 3] [LEVEL 4]
>
> Therefor 'hiding' the first two levels.
>
> Regards,
> Brett
>
>
>
>
> "MIKE215" wrote:
>
> > Hi Brett
> > Set the visible property of the item to false. If you place this code in a
> > loop just remember to leave at least one item visible to avoid an error.
> >
> > ActiveSheet.PivotTables(1).PivotFields(2).PivotItems(2).Visible = False
> >
> > Regards
> > Mike
> >
> > "Brett" wrote:
> >
> > > I was wondering how you'd hide levels in a Pivot Table using VBA. e.g. the
> > > way it's currently done is you right click the level and click on "Hide
> > > Levels" on the pivot table, how can I reproduce that functionality in VBA?
> > >
> > > thanks.
> > >