VBA Pivot Table Question

  • Thread starter Thread starter Jack Clifford
  • Start date Start date
J

Jack Clifford

I have a pivot table and some pivot items are hidden. When
I want to display them using VB code it fails.

I tried both the following statements neither works. Both
statements work to hide the pivot when visible = True.

Any suggestions?

These Work (the selected Pivot Item is hidden)

Worksheets("Summary").PivotTables(1).PivotFields("Org") _
.PivotItems("Item Name").Visible = False

With ActiveSheet.PivotTables _
("PivotTable1").PivotFields("Org")
.PivotItems("Item Name").Visible = False
End With


These Fail (The selected pivot item is not displayed)

Worksheets("Summary").PivotTables(1).PivotFields("Org") _
.PivotItems("Item Name").Visible = True

With ActiveSheet.PivotTables _
("PivotTable1").PivotFields("Org")
.PivotItems("Item Name").Visible = True
End With
 
Set AutoSort to manual, and you'll be able to set visible to True.

With ActiveSheet.PivotTables _
("PivotTable1").PivotFields("Org")
.AutoSort xlManual, "Org"
.PivotItems("Item Name").Visible = True
End With
 
Back
Top