Hide sheets after the particular sheet name occurrence

MKR 0
Viewed 33

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

enter image description here

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

vba excel
2 Answers

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
FaneDuru0

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.

Stefø0
Related