Hide sheets after the particular sheet name occurrence-CodePudding

I have a multitude of sheets in my workbook. I want to hide some of them. The primary criterion can be hiding them beyond the sheet with some specified name.

In my case, this is the sheet named BoM

I want everything to be hidden behind this worksheet.

What I tried is:

 Sub Sheethidden()
 Dim ws As Worksheet

  For Each ws In Worksheets
    If ws.Name > "BoM" Then
        ws.Visible = xlSheetHidden
    End If
Next ws

End Sub

but it didn't work

CodePudding user response：

Please, try the next way. You need to use the sheet Index as reference:

Sub Sheethidden()
 Dim wsIndex As Long, i As Long
 wsIndex = Worksheets("BoM").Index
  For i = wsIndex   1 To Worksheets.count
        Worksheets(i).Visible = xlSheetHidden
  Next i
End Sub

or you may keep your code and compare the sheets index:

Sub Sheethidden()
 Dim ws As Worksheet

  For Each ws In Worksheets
    If ws.index > Worksheets("BoM").Index Then
        ws.Visible = xlSheetHidden
    End If
  Next ws

End Sub

CodePudding user response：

Adapting your code, I would do as follow.

Public Sub HideSheetAfterName()

    Const NAME_SHEET = "BoM"
    Dim ws As Worksheet
    Dim ws_pass As Boolean

    ws_pass = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = NAME_SHEET Then ws_pass = True
        If ws_pass Then ws.Visible = xlSheetHidden
    Next ws

End Sub

Since you know Excel worksheets in ThisWorkbook.Worksheets are ordered by order they appear in the Excel workbook, I suggest just to check when you find the desired NAME_SHEET.

Additional suggestion: always use ThisWorkbook before Worksheet object in order to force using the current workbook.