ExcelBanter - Pivot Change PivotItems

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Pivot Change PivotItems - Visible/Hide with VBA (https://www.excelbanter.com/excel-worksheet-functions/124722-pivot-change-pivotitems-visible-hide-vba.html)

Pivot Change PivotItems - Visible/Hide with VBA

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

Pivot Change PivotItems - Visible/Hide with VBA

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

Romanoff wrote:
Hello Debra

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

Roman

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

wrote:

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
Contextures
http://www.contextures.com/tiptech.html

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html