Macro to hide worksheet based upon text in the name
in [General]
|
From: Joe M. on 8 Apr 2010 11:38 Is it possible to have a macro that hides worksheets based upon text contained or not contained in the worksheet name? Suppose i have several worksheets but want to hide ony the ones that don't have the text "HOLD" in the name; worksheet names: Data_HOLD Data Selection_HOLD etc.... or maybe base it upon the WS tab color.... Thanks, Joe M.
From: Mike H on 8 Apr 2010 11:43 Hi, This will hide all worksheets with Hold anywhere in the name Sub Hide_shts() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If InStr(1, ws.Name, "Hold", vbTextCompare) Then ws.Visible = False End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Joe M." wrote: > Is it possible to have a macro that hides worksheets based upon text > contained or not contained in the worksheet name? Suppose i have several > worksheets but want to hide ony the ones that don't have the text "HOLD" in > the name; > > worksheet names: > Data_HOLD > Data Selection_HOLD > etc.... > or maybe base it upon the WS tab color.... > > Thanks, > Joe M. >
From: Joe M. on 8 Apr 2010 11:55 Thanks. But I have discovered that I will have to rename some other sheets for this to work and it will affect some macros. What about by the WS tab color? Tis will be easier for me. Sorry for not being sure earlier. Thx, Joe M. "Mike H" wrote: > Hi, > > This will hide all worksheets with Hold anywhere in the name > > Sub Hide_shts() > Dim ws As Worksheet > For Each ws In ThisWorkbook.Worksheets > If InStr(1, ws.Name, "Hold", vbTextCompare) Then > ws.Visible = False > End If > Next > End Sub > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Joe M." wrote: > > > Is it possible to have a macro that hides worksheets based upon text > > contained or not contained in the worksheet name? Suppose i have several > > worksheets but want to hide ony the ones that don't have the text "HOLD" in > > the name; > > > > worksheet names: > > Data_HOLD > > Data Selection_HOLD > > etc.... > > or maybe base it upon the WS tab color.... > > > > Thanks, > > Joe M. > >
From: Mike H on 8 Apr 2010 12:01 Hi, This will hide sheets with red tabs Sub Hide_shts() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Tab.Color = 255 Then ws.Visible = False End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Joe M." wrote: > Thanks. But I have discovered that I will have to rename some other sheets > for this to work and it will affect some macros. What about by the WS tab > color? Tis will be easier for me. Sorry for not being sure earlier. > > Thx, > Joe M. > > "Mike H" wrote: > > > Hi, > > > > This will hide all worksheets with Hold anywhere in the name > > > > Sub Hide_shts() > > Dim ws As Worksheet > > For Each ws In ThisWorkbook.Worksheets > > If InStr(1, ws.Name, "Hold", vbTextCompare) Then > > ws.Visible = False > > End If > > Next > > End Sub > > > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "Joe M." wrote: > > > > > Is it possible to have a macro that hides worksheets based upon text > > > contained or not contained in the worksheet name? Suppose i have several > > > worksheets but want to hide ony the ones that don't have the text "HOLD" in > > > the name; > > > > > > worksheet names: > > > Data_HOLD > > > Data Selection_HOLD > > > etc.... > > > or maybe base it upon the WS tab color.... > > > > > > Thanks, > > > Joe M. > > >
From: Joe M. on 8 Apr 2010 12:16 I tried with several WS with red tabs but it only hides one. I double checked to make sure red was selected. What is wrong? Thanks, Joe M. "Mike H" wrote: > Hi, > > This will hide sheets with red tabs > > Sub Hide_shts() > Dim ws As Worksheet > For Each ws In ThisWorkbook.Worksheets > If ws.Tab.Color = 255 Then > ws.Visible = False > End If > Next > End Sub > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Joe M." wrote: > > > Thanks. But I have discovered that I will have to rename some other sheets > > for this to work and it will affect some macros. What about by the WS tab > > color? Tis will be easier for me. Sorry for not being sure earlier. > > > > Thx, > > Joe M. > > > > "Mike H" wrote: > > > > > Hi, > > > > > > This will hide all worksheets with Hold anywhere in the name > > > > > > Sub Hide_shts() > > > Dim ws As Worksheet > > > For Each ws In ThisWorkbook.Worksheets > > > If InStr(1, ws.Name, "Hold", vbTextCompare) Then > > > ws.Visible = False > > > End If > > > Next > > > End Sub > > > > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "Joe M." wrote: > > > > > > > Is it possible to have a macro that hides worksheets based upon text > > > > contained or not contained in the worksheet name? Suppose i have several > > > > worksheets but want to hide ony the ones that don't have the text "HOLD" in > > > > the name; > > > > > > > > worksheet names: > > > > Data_HOLD > > > > Data Selection_HOLD > > > > etc.... > > > > or maybe base it upon the WS tab color.... > > > > > > > > Thanks, > > > > Joe M. > > > >
|
Next
|
Last
Pages: 1 2 Prev: Format Question Next: Declaring variables for use in multiple worksheets |