• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide Worksheets based on Value in a range


New Member
Hi Guys,

I have a workbook with 300 or so worksheets. One of the worksheets called "Front Sheet" contains 2 columns (A & B). Column A lists the names of each of the tabs that I wish to assess and hide. Column B Lists a numerical value. I want a VBA to loop through column B and if the value in that row is a zero (0) then to find to find the corresponding tab from the list of tab names in column A and hide that sheet.
Note: There are other tabs in the workbook whose names do no not appear in Column A of worksheet "Front Sheet" the VBA should not do anything to those tabs.
Any assistance would be hugely appreciated.

many Thanks




Active Member
Option Explicit

Sub HideTabs()
    Dim ws As Worksheet, s1 As Worksheet
    Dim N As String
    Set s1 = Sheets("Front Sheet")
    Dim i As Long, lr As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 4 To lr
        If s1.Range("B" & i) = 0 Then
            N = s1.Range("A" & i)
            Sheets(N).Visible = False
        End If
    Next i

End Sub
Will you need code to Unhide?


Active Member
Sub Mofi454()
   Dim Cl As Range
   With Sheets("Front Sheet")
      For Each Cl In .Range("A4", .Range("A" & Rows.Count).End(xlUp))
         If Evaluate("isref('" & CStr(Cl.Value) & "'!a1)") Then
            Sheets(CStr(Cl.Value)).Visible = Cl.Offset(, 1) <> 0
         End If
      Next Cl
   End With
End Sub
This will hide & unhide the listed sheets


New Member
No I wont need to unhide. I've written something to unhide all sheets. I just wanted to say thank you so much for taking the time to write this.