How to hide worksheet when certain criteria exist

Question

I am trying to write a code that loop through to hide worksheet tab automatically if certain name exists then add column with vlookup. If none of this names exists do nothing. if I didn't manually comment the . I am trying to hide if it exist (Michael , Jami , Stam, Christina) if they exist I want to hide them if none of those names exist do nothing in the code, It is giving me an error.

Sub Admin_Auto_Add()

    Dim rec_range As String
    Dim wb As Workbook
    Dim lookup_reference As String
    
    With Original
        
        ActiveWorkbook.Sheets("Michael").Visible = xlSheetHidden
        ActiveWorkbook.Sheets("Jami").Visible = xlSheetHidden
'        ActiveWorkbook.Sheets("Stam").Visible = xlSheetHidden
        ActiveWorkbook.Sheets("Christina").Visible = xlSheetHidden
        
       Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
       Range("C1").Value = "Admin_Vlookup"
      
        rec_range = getColRangeFunction("Admin_Vlookup")
        Range(rec_range).Formula = "=VLOOKUP(B2,'[Pairing List.xlsx]Recruiting_Admins'!$A$1:$B$32, 2,0)"
        Range(rec_range).Select
    End With
End Sub

How are you determining which sheet to hide and when to hide it? In the code you posted it appears you are hiding all 4 sheets regardless. — norie, Feb 26 '21 at 17:45
I am new to VBA and I don't know how to loop through to tell the code which one to hide or which one not based on scenario. Thats Why I need help thank you — Jake8281, Feb 26 '21 at 17:46
Here is an example of [how to loop through worksheets](https://stackoverflow.com/questions/25953916/excel-vba-looping-through-multiple-worksheets). Take a stab at trying to apply this to your code and come back if you have a specific issue trying to implement. In each instance of the loop, you can refer to the current worksheet via the variable `ws`. So apply the same logic to all sheets. If you need condition then `If ws.Name = "Michael" Then [Test this criteria] ElseIf ws.Name = "urdearboy" Then [Test This criteria]` — urdearboy, Feb 26 '21 at 17:48
It's straightforward to loop through sheets but you aren't telling us how to determine whether a sheet should hidden or not. You mention something about a 'name' existing, can you expand on that? — norie, Feb 26 '21 at 17:49
So the worksheets I am trying to hide if it exist . Michael , Jami , Stam, Christina if they exist I want to hide them if not do nothing — Jake8281, Feb 26 '21 at 17:52

score 4 · Accepted Answer · answered Feb 26 '21 at 17:57

Perhaps something like this then.

Sub HideSheets()
Dim ws As Worksheet
Dim arrNames As Variant
Dim Res As Variant

    ' add/remove/change names of sheets you want to hide here
    arrNames = Array("Michael", "Jami", "Stam", "Christina")
   
    For Each ws In ActiveWorkbook.Sheets
        Res = Application.Match(ws.Name, arrNames, 0)
        
        If Not IsError(Res) Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
    
End Sub

How to hide worksheet when certain criteria exist

1 Answers1