For Each Next, Case Select

[Guest]

I would like to get the code below to work by looking for each PivotItem and
if it exists, set the Visible property to False. I've tried to nest a Case
Select within a For Each loop but I can not get this to work for me.

Any and all help is greatly appreciated.



With ActiveSheet.PivotTables("PivotTable1").PivotFields("ENGINE STATUS")
.PivotItems("IN-SP").Visible = False
.PivotItems("SA-FA").Visible = False
.PivotItems("SD-WF").Visible = False
.PivotItems("SD-WR").Visible = False
.PivotItems("TS367-094").Visible = False
.PivotItems("TS367-096").Visible = False
.PivotItems("TS-DP").Visible = False
.PivotItems("TS-FA").Visible = False
.PivotItems("TS-SA").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("ENGINE STATUS")
.PivotItems("LD-RV").Visible = False
.PivotItems("TS367-092").Visible = False
.PivotItems("TS-DP").Visible = False
.PivotItems("TS-EN").Visible = False
.PivotItems("TS-FA").Visible = False
End With

 

[Guest]

I haven't tested the code below but I think both methods should work

Method 1:

for TableCount = 1 to 10
With ActiveSheet.PivotTables("PivotTable" &
TableCount1).PivotFields("ENGINE STATUS")
.PivotItems("IN-SP").Visible = False
.PivotItems("SA-FA").Visible = False
.PivotItems("SD-WF").Visible = False
.PivotItems("SD-WR").Visible = False
.PivotItems("TS367-094").Visible = False
.PivotItems("TS367-096").Visible = False
.PivotItems("TS-DP").Visible = False
.PivotItems("TS-FA").Visible = False
.PivotItems("TS-SA").Visible = False
End With
Next TableCount

Method 2:

for each PT in ActiveSheet.PivotTables
With PT.PivotFields("ENGINE STATUS")
.PivotItems("IN-SP").Visible = False
.PivotItems("SA-FA").Visible = False
.PivotItems("SD-WF").Visible = False
.PivotItems("SD-WR").Visible = False
.PivotItems("TS367-094").Visible = False
.PivotItems("TS367-096").Visible = False
.PivotItems("TS-DP").Visible = False
.PivotItems("TS-FA").Visible = False
.PivotItems("TS-SA").Visible = False
End With
next PT

 

[Guest]

Thanks for the reply Joel. I'll give these a try and post back on the
results. The problem I faced with just using the WITH option is that if I
added a potential field that doesn't exist in the data set I'm working with,
it caused the code to bomb. In other words, the active PivotItems change form
day to day on the data I collect.

Kind Regards,