Pivot Change PivotItems - Visible/Hide with VBA

[rkuemin]

Hello

I've got a problem with my pivot.

Here is some simple code: I would like to hide all Items but "10
Property"

Sub showOnlyProperty()
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Main line
of business")
.PivotItems("15 Liability").Visible = False
.PivotItems("20 Motor").Visible = False
.PivotItems("25 Marine").Visible = False
.PivotItems("30 Aviation").Visible = False
.PivotItems("35 Engineering").Visible = False
End With
End Sub

With this code I set not visible several Items in a RowField.

After that, I would like to do the following: Show "20 Motor" instead
of "10 Property"

Sub showMotorafterProperty()
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Main line
of business")
.PivotItems("20 Motor").Visible = True
.PivotItems("10 Property").Visible = False
End With
End Sub


This is not possible with VBA...the following ErrorMessage appear:
"Unable to set the Visible property of the PivotItem class"

Why this message? I do not understand this.
What do I have to modify at this code?
Do I have to simulate the User-Mouse-Klick on the Filter on the Pivot
(how?) or there is any other tipps ?


Thanks, Roman

 

[Debra Dalgleish]

To prevent the error, set the Sort for the field to Manual. You can do
this in the code, for example:

Sub showMotorafterProperty()
With ActiveSheet.PivotTables("PivotTable3") _
.PivotFields("Main line of business")
.AutoSort xlManual, "Main line of business"
.PivotItems("20 Motor").Visible = True
.PivotItems("10 Property").Visible = False
End With
End Sub

 

[Romanoff]

Hello Debra

Thanks a lot for this simple solution!
It works fine.


Roman

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.

Hello Debra

Thanks a lot for this simple solution!
It works fine.


Roman