Hide sheet names

[Monty]

Hello Experts.

Please need piece of code on the below query.

on main sheet we have list of sheet names to hide on click of a button.
and those names can be changed by user based on requirement.

So we need to hide sheets based on the names mentioned in the A column of the main sheet..

thanking you in advance


File attatched!

 

[YasserKhalil]

Try this code

Sub Hide_Sheets()
    Dim I As Integer
   
    I = 3
    Do While Cells(I, 1).Value <> ""
        ThisWorkbook.Worksheets(Cells(I, 1).Value).Visible = False
        I = I + 1
    Loop
End Sub

 

[Monty]

YasserKhalil!

You are champ..Great job.

 

[Marc L]

Hi !

Yasser, it may be tricky if Main worksheet is not the active worksheet !
(TBTO rule …)​

Sub Demo()
    With Sheet1.Cells(1).CurrentRegion
        For Each V In .Cells(3, 1).Resize(.Rows.Count - 2).Value
            ThisWorkbook.Worksheets(V).Visible = False
        Next
    End With
End Sub

If Sheet1 is not the CodeName of Main worksheet,
it can be replaced either by ThisWorkbook.Worksheets("Main")

or by ThisWorkbook.Worksheets(1) …
​

 

[Monty]

Hello Mark/ YasserKhalil
Thank you so very much for the quick response.

can we do this other way round..

hide all the sheets in the workbook expect the sheet names mentioned on the first sheet.. i do not want to mention sheet names in the code and make it static..

Thank you.

 

[YasserKhalil]

May be something like that

Sub Test()
    Dim I As Integer, J As Integer, P As Integer, vAns
    Dim Sh As Worksheet
    ReDim mArray(0 To Application.WorksheetFunction.CountA(Sheet1.Columns(1)) - 2)
    ReDim Arr(0 To Sheets.Count - UBound(mArray) - 2)

    For I = 3 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
        mArray(I - 3) = Sheet1.Cells(I, 1).Value
    Next I

    For I = 1 To Sheets.Count
        vAns = ""
        On Error Resume Next
            vAns = Application.WorksheetFunction.Match(Sheets(I).Name, mArray, 0)
        On Error GoTo 0

        If vAns = "" Then
            Arr(P) = ThisWorkbook.Sheets(I).Name
            P = P + 1
        End If
    Next I
   
    For Each Sh In ThisWorkbook.Worksheets
        Sh.Visible = True
    Next Sh
   
    For J = 0 To UBound(Arr)
        Sheets(Arr(J)).Visible = False
    Next J
End Sub

 

[Marc L]

Yasser, no WorksheetFunction, no more On Error ‼

Never use it but just Application with a Variant variable
you could test using IsError and IsNumeric VBA functions …​

hide all the sheets in the workbook expect the sheet names mentioned on the first sheet

So if "expect" means except :​

Sub Demo2()
        VA = Sheet1.Cells(3, 1).Resize(Sheet1.Cells(1).CurrentRegion.Rows.Count - 2).Value
    For W& = 2 To ThisWorkbook.Worksheets.Count
        With ThisWorkbook.Worksheets(W)
            .Visible = IsNumeric(Application.Match(.Name, VA, 0))
        End With
    Next
End Sub

Respecting TEBV & TBTO rules, that's easy, isn't it ?!
​

 

[YasserKhalil]

Wonderful Mr. MarcL
You are GREAT ..
Best Regards