In Excel (VBA) how can I hide worksheets based on values in table column-CodePudding

I have spreadsheet with around 20 worksheets. On one of the worksheets I have a one column table called CategoriesTable. In this table I want to input the names of the worksheets that I want to remain visible then use VBA to hide the rest.

I can kind of achieve this by adding another column and putting in a value of something like hidden or unhide but I was hoping someone could help with a more efficient way of doing this with just the one column.

Thanks for any help.

CodePudding user response：

Please, try the next code:

Sub HideSheetsFromColumn()
   Dim sh As Worksheet, ws As Worksheet, lastR As Long, arr, El, mtch
   
   Set sh = ActiveSheet 'use here the sheet keeping the sheets to not be hide
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'last row. If not A:A column, use here the appropriate letter
   arr = sh.Range("A2:A" & lastR).Value
   
        For Each ws In ActiveWorkbook.Worksheets
            mtch = Application.match(ws.Name, arr, 0)
            If IsError(mtch) Then 'not found in the list
                ws.Visible = xlSheetVeryHidden
            Else
                ws.Visible = xlSheetVisible 'for the case you change your mind and delete the name from the range...
            End If
        Next ws
End Sub

You must know that I made an exception and post an answer. Please, note the it is mandatory to prove that you tried something by your own and ask as in case your attempt does not work as you need...