Exclude Very Hidden Worksheets from Menu / Index of All Sheets

[BDM AT WCS]

I've got a Workbook report that pulls 'raw' source data in via MS Query. I don't want the report recipients to see this RAW DATA and also some other 'helper' worksheets so I've set those to be VeryHidden. Since there are a large number of other visible sheets, I've copied a macro which produces a list of all sheets, assigns an index number to them and creates hyperlinks for each to quickly navigate around the workbook and reproduces the worksheet colour in the next column. But this macro includes ALL worksheets and I would like to exclude the VeryHidden sheets from the menu. Can anyone help?

Sub MenuOfSheetsByTab()

'Go to the "menu" worksheet
Sheets("menu").Select
'list all sheets in active workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim wsM As Worksheet
Dim lColorC As Long 'cell color
Dim lColorT As Long 'tab color
Dim lRow As Long
Dim lRowHead As Long
Dim lCol As Long
Dim lCols As Long
Dim lColEnd As Long
Dim lSh As Long
On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set wsM = Worksheets("menu")
lRowHead = 1
lCol = 1
lCols = 2
lColEnd = lCol + lCols - 1
lRow = lRowHead + 1

With wsM.Range(wsM.Cells(lRowHead, lCol), _
    wsM.Cells(lRowHead, lColEnd))
  .EntireColumn.Clear
  .Value = Array("ID", "Sheet")
End With

With wsM
  For Each ws In wb.Worksheets
    .Range(.Cells(lRow, lCol), _
      .Cells(lRow, lColEnd - 1)).Value _
      = Array(lRow - lRowHead, ws.Name)
      If ws.Tab.ColorIndex = -4142 Then
        .Cells(lRow, lColEnd) _
          .Interior.ColorIndex _
          = ws.Tab.ColorIndex

      Else
        .Cells(lRow, lColEnd) _
          .Interior.Color _
          = ws.Tab.Color
      End If

'add hyperlink to sheet name
      .Hyperlinks.Add _
          Anchor:=.Cells(lRow, lCol + 1), _
          Address:="", _
          SubAddress:="'" & ws.Name & "'!A1", _
          ScreenTip:=ws.Name, _
          TextToDisplay:=ws.Name
    lRow = lRow + 1
  Next ws

  With .Range(.Cells(lRowHead, lCol), _
      .Cells(lRowHead, lColEnd))
    .Font.Bold = True
    .EntireColumn.AutoFit

End With
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Oaktree]

With wsM
  For Each ws In wb.Worksheets
    If ws.Visible <> xlVeryHidden then
     ... rest of code...
    End If
  Next ws
End With

 

[BDM AT WCS]

Thanks for your lighting fast response Oaktree. It took me a bit of time to figure out where this code needs to be inserted into the whole macro but I got there in the end
One thing though, the way I got it to work was to insert your suggested "Next ws" AFTER your "End with". I've inserted comments into the code for others to follow...
I think there may be some superfluous " With wsM" and "End With" in my version so smarter / more experienced minds could render a version with cleaner code, but this works. It could well be that I've not inserted Oaktree's code where he'd intended I should so maybe he got the "Next ws" and "End with" in the right order and that's why I've ended up with some additional and perhaps unnecessary code.

Sub MenuOfSheetsByTab()
'Go to the "menu" tab
    Sheets("menu").Select

'list all sheets in active workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim wsM As Worksheet
Dim lColorC As Long 'cell color
Dim lColorT As Long 'tab color
Dim lRow As Long
Dim lRowHead As Long
Dim lCol As Long
Dim lCols As Long
Dim lColEnd As Long
Dim lSh As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set wsM = Worksheets("menu")
lRowHead = 1
lCol = 1
lCols = 3
lColEnd = lCol + lCols - 1
lRow = lRowHead + 1

With wsM.Range(wsM.Cells(lRowHead, lCol), _
    wsM.Cells(lRowHead, lColEnd))
  .EntireColumn.Clear
  .Value = Array("ID", "Sheet", "Tab Colour")
End With
      
'start of Oaktree's solution

With wsM
  For Each ws In wb.Worksheets
  With wsM
    If ws.Visible <> xlVeryHidden Then

'here is where Oaktree's "...rest of code..." starts

    .Range(.Cells(lRow, lCol), _
      .Cells(lRow, lColEnd - 1)).Value _
      = Array(lRow - lRowHead, ws.Name)
      If ws.Tab.ColorIndex = -4142 Then
        .Cells(lRow, lColEnd) _
          .Interior.ColorIndex _
          = ws.Tab.ColorIndex
      Else
        .Cells(lRow, lColEnd) _
          .Interior.Color _
          = ws.Tab.Color
      End If


      'add hyperlink to sheet name
      .Hyperlinks.Add _
          Anchor:=.Cells(lRow, lCol + 1), _
          Address:="", _
          SubAddress:="'" & ws.Name & "'!A1", _
          ScreenTip:=ws.Name, _
          TextToDisplay:=ws.Name
    lRow = lRow + 1
    
'here is where Oaktree's solution starts to close
'The "End If" immediately below closes the original "If ws.Visible <> xlVeryHidden Then"
    End If
'The "End With" immediately below closes the original "With wsM"
    End With
'And then the code loops back via "Next ws" to look at the next worksheet in sequence. If it's not equal to VeryHidden (<> xlVeryHidden) then it applies the code in between to the visible worksheet
  Next ws
 
  With .Range(.Cells(lRowHead, lCol), _
      .Cells(lRowHead, lColEnd))
    .Font.Bold = True
    .EntireColumn.AutoFit
  End With
End With
 
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Oaktree]

You don't need the 2nd With, as it's redundant with the 1st:

Sub MenuOfSheetsByTab()
'Go to the "menu" tab
    Sheets("menu").Select

'list all sheets in active workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim wsM As Worksheet
Dim lColorC As Long 'cell color
Dim lColorT As Long 'tab color
Dim lRow As Long
Dim lRowHead As Long
Dim lCol As Long
Dim lCols As Long
Dim lColEnd As Long
Dim lSh As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set wsM = Worksheets("menu")
lRowHead = 1
lCol = 1
lCols = 3
lColEnd = lCol + lCols - 1
lRow = lRowHead + 1

With wsM.Range(wsM.Cells(lRowHead, lCol), _
    wsM.Cells(lRowHead, lColEnd))
  .EntireColumn.Clear
  .Value = Array("ID", "Sheet", "Tab Colour")
End With
      
'start of Oaktree's solution

With wsM
  For Each ws In wb.Worksheets
    If ws.Visible <> xlVeryHidden Then

'here is where Oaktree's "...rest of code..." starts

    .Range(.Cells(lRow, lCol), _
      .Cells(lRow, lColEnd - 1)).Value _
      = Array(lRow - lRowHead, ws.Name)
      If ws.Tab.ColorIndex = -4142 Then
        .Cells(lRow, lColEnd) _
          .Interior.ColorIndex _
          = ws.Tab.ColorIndex
      Else
        .Cells(lRow, lColEnd) _
          .Interior.Color _
          = ws.Tab.Color
      End If

      'add hyperlink to sheet name
      .Hyperlinks.Add _
          Anchor:=.Cells(lRow, lCol + 1), _
          Address:="", _
          SubAddress:="'" & ws.Name & "'!A1", _
          ScreenTip:=ws.Name, _
          TextToDisplay:=ws.Name
    lRow = lRow + 1
    
'here is where Oaktree's solution starts to close
'The "End If" immediately below closes the original "If ws.Visible <> xlVeryHidden Then"
    End If
'And then the code loops back via "Next ws" to look at the next worksheet in sequence. If it's not equal to VeryHidden (<> xlVeryHidden) then it applies the code in between to the visible worksheet
  Next ws
 
  With .Range(.Cells(lRowHead, lCol), _
      .Cells(lRowHead, lColEnd))
    .Font.Bold = True
    .EntireColumn.AutoFit
  End With
End With
 
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[BDM AT WCS]

I commented out the 2nd End With rather than deleting. I then got a "Next without For" compile error as commented into the code below

'here is where Oaktree's solution starts to close
'The "End If" immediately below closes the original "If ws.Visible <> xlVeryHidden Then"
    End If
'The "End With" immediately below closes the original "With wsM"
'commented out the 2nd end "End With" here....and got a compile error at Next ws
'And then the code loops back via "Next ws" to look at the next worksheet in sequence. If it's not equal to VeryHidden (<> xlVeryHidden) then it applies the code to the visible worksheet
  Next ws
'"Next without For" Compile error here'

 

[Oaktree]

Did you also comment out the 2nd "With ws"? The code in my previous post removed both the 2nd "With ws" and its corresponding "End with". If you left one but not the other, it will error out.

 

[BDM AT WCS]

You nailed it Oaktree! Thanks for your help today. Awesome!

Sub MenuOfSheetsByTab()

'Go to the "menu" tab
    Sheets("menu").Select
'list all sheets in active workbook

Dim wb As Workbook
Dim ws As Worksheet
Dim wsM As Worksheet
Dim lColorC As Long 'cell color
Dim lColorT As Long 'tab color
Dim lRow As Long
Dim lRowHead As Long
Dim lCol As Long
Dim lCols As Long
Dim lColEnd As Long
Dim lSh As Long
On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set wsM = Worksheets("menu")

lRowHead = 1
lCol = 1
lCols = 3
lColEnd = lCol + lCols - 1
lRow = lRowHead + 1

With wsM.Range(wsM.Cells(lRowHead, lCol), _
    wsM.Cells(lRowHead, lColEnd))
  .EntireColumn.Clear
  .Value = Array("ID", "Sheet", "Tab Colour")
End With

'start of Oaktree's solution

With wsM

  For Each ws In wb.Worksheets
    If ws.Visible <> xlVeryHidden Then

'here is where Oaktree's "...rest of code..." starts

    .Range(.Cells(lRow, lCol), _
      .Cells(lRow, lColEnd - 1)).Value _
      = Array(lRow - lRowHead, ws.Name)
      If ws.Tab.ColorIndex = -4142 Then
        .Cells(lRow, lColEnd) _
          .Interior.ColorIndex _
          = ws.Tab.ColorIndex
      Else
        .Cells(lRow, lColEnd) _
          .Interior.Color _
          = ws.Tab.Color
      End If

      'add hyperlink to sheet name

      .Hyperlinks.Add _
          Anchor:=.Cells(lRow, lCol + 1), _
          Address:="", _
          SubAddress:="'" & ws.Name & "'!A1", _
          ScreenTip:=ws.Name, _
          TextToDisplay:=ws.Name
    lRow = lRow + 1

'here is where Oaktree's solution starts to close

'The "End If" immediately below closes the original "If ws.Visible <> xlVeryHidden Then"
    End If

'And then the code loops back via "Next ws" to look at the next worksheet in sequence. If it's not equal to VeryHidden (<> xlVeryHidden) then it applies the code to the visible worksheet
  Next ws

'...this "With" and "End With" below was in the original macro
  With .Range(.Cells(lRowHead, lCol), _
      .Cells(lRowHead, lColEnd))
    .Font.Bold = True
    .EntireColumn.AutoFit
  End With

'...and here is Oaktree's final "End With" to close out the "With wsM" at the start of his solution
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub