|
For Each Next, Case Select
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 |
For Each Next, Case Select
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 "Hal" wrote: 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 |
For Each Next, Case Select
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, "Joel" wrote: 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 "Hal" wrote: 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 |
| All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com