Thread: Showing specific pivot items and hiding all others instead of viceversa

Hi everyone,

I would like to start off by saying I have been able to solve most of my work-related inquiries by browsing these forums and the help has been great!

I've run into an issue that I haven't been able to find the answer to by browsing current help topics on these forums.

Every month I have to create a pivot table that shows only two business groups and hides all the rest. Currently I have had to go through and edit the code as such for the field filters:

With ActiveSheet.PivotTables("SamplePivot").PivotFields("Grp")
.PivotItems("C EQ").Visible = False
.PivotItems("F SVC").Visible = False
.PivotItems("G RES").Visible = False
.PivotItems("Grp").Visible = False
.PivotItems("H SC").Visible = False
.PivotItems("T FAC").Visible = False
.PivotItems("U FIN").Visible = False
.PivotItems("V HRS").Visible = False
.PivotItems("W ITS").Visible = False
.PivotItems("(blank)").Visible = False
End With

The problem I run into is that these groups aren't always the same, while as the other two that aren't listed as invisible are always the same. When I receive a file that doesn't have one of the aforementioned fields, the macro returns an error for invalid object.

I'm wondering if there is a way to simply set the two fields that stay constant as visible, and hide all the rest? I've tried coding an "If-Then-Else" statement but haven't had any luck getting the code to work correctly. Your help is appreciated in advance!

-Alex

For each PvtItm in ActiveSheet.PivotTables("SamplePivot").PivotFields("Grp").PivotItems
     If PvtItm.Name = "First Name" or PvtItm.Name = "Second Name" Then
          PvtItm.Visible = TRUE
     Else
          pvtItm.Visible = FALSE
     End If
Next PvtItm

The above was just typed in the comment window. I haven't tested it in any way.

Originally Posted by Whizbang

For each PvtItm in ActiveSheet.PivotTables("SamplePivot").PivotFields("Grp").PivotItems
     If PvtItm.Name = "First Name" or PvtItm.Name = "Second Name" Then
          PvtItm.Visible = TRUE
     Else
          pvtItm.Visible = FALSE
     End If
Next PvtItm

The above was just typed in the comment window. I haven't tested it in any way.

Home run on the first try. Thank you for the help! Setting as solved.