For Each Next, Case Select

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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,
 
Back
Top