Hide/Unhide Tabs that Contain a Word.

[Robert_Conklin]

Good evening, I have the below sub that is set to hide all Tabs with a name that ends in h. I would like to modify it to use the same button to Hide/Unhide all tabs that contain a word instead of ends with a letter.

Option Explicit


'Set tab naming convention to hide & unhide
Const TABNAME As String = "-h"


Sub Hide_Named_Sheets()
'Hide all sheets that end with -h

Dim ws As Object 'Use object instead of worksheet for Chartsheets

  'Hide sheets with sheet name ending in -h
  For Each ws In ActiveWorkbook.Sheets
    If Right(ws.Name, 2) = TABNAME Then
      ws.Visible = xlSheetHidden
    End If
  Next ws

End Sub

Any help would be greatly appreciated!!

 

[Michael M]

What word ??

 

[Michael M]

Change "WORD" to your word !!

Sub Hide_Named_Sheets()
'Hide all sheets that end with -h
Dim ws As Object 'Use object instead of worksheet for Chartsheets
  For Each ws In Sheets
    If InStr(ws.Name, "WORD") Then
      ws.Visible = xlSheetHidden
    End If
  Next ws
End Sub

 

[Robert_Conklin]

Change "WORD" to your word !!

Sub Hide_Named_Sheets()
'Hide all sheets that end with -h
Dim ws As Object 'Use object instead of worksheet for Chartsheets
  For Each ws In Sheets
    If InStr(ws.Name, "WORD") Then
      ws.Visible = xlSheetHidden
    End If
  Next ws
End Sub

That worked. I added the "Not ws.Visible" that we applied in the other code you helped me with and now the same button hides and unhides the worksheets. You're the best!

 

[Michael M]

Glad to help.....

 

[thedoccontroller]

That worked. I added the "Not ws.Visible" that we applied in the other code you helped me with and now the same button hides and unhides the worksheets. You're the best!

How did you add the "Not"?

 

[Michael M]

Sub Hide_Named_Sheets()
Dim ws As worksheet
  For Each ws In workSheets
      ws.Visible = Not ws.Visible
  Next ws
End Sub

 

[thedoccontroller]

Sub Hide_Named_Sheets()
Dim ws As worksheet
  For Each ws In workSheets
      ws.Visible = Not ws.Visible
  Next ws
End Sub

Brilliant! TY

 

[Michael M]

Glad to help ?

 

[Robert_Conklin]

Option Explicit


'Set tab naming convention to hide & unhide
Const TABNAME As String = "WORD"

Sub Hide_Unhide_Named_Sheets()
'Hide all sheets that contain a specific word
Dim ws As Object 'Use object instead of worksheet for Chartsheets
  For Each ws In Sheets
    If InStr(ws.Name, "WORD") Then 'Specify the word you want to use.
      [COLOR=rgb(247, 218, 100)]ws.Visible = Not ws.Visible[/COLOR]
    End If
  Next ws
End Sub