For Each Next, Case Select

H

Hal

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
 
J

Joel

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
 
H

Hal

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top