Hide sheets after the particular sheet name occurrence

September 7, 2022

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

>Solution :

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