• 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

    Hui...

  • 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

Mofi454

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

Mofi
 

Attachments

AlanSidman

Active Member
Code:
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?
 

Fluff13

Active Member
Code:
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
 

Mofi454

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.
 
Top