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).PivotIte ms(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).PivotIte ms(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).PivotIte ms(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.