CODEWITHSUNDEEP
excelvba
Jake8281
Jake8281

Reputation: 71

How to hide worksheet when certain criteria exist

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

Upvotes: 2

Views: 241

Answers (1)

norie
norie

Reputation: 9857

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

Upvotes: 4

Related Questions