I have a question about the macro code I am trying to use that hides sheets in my workbook based on the text of a specific cells in another worksheet. The code seems to work, and I could just make several modules using the code and tweaking it, but before I do that I would like to know if I can just use one module to accomplish my goal.
Worksheet "A" has several cells in the B151:B157 range that have data validation drop downs for selecting Yes or No. If the Yes option is selected, I want to keep that worksheet visible in the workbook. If the No option is selected, I want to hide the worksheet. Each cell goes with a different worksheet. So 151 would go with Worksheet 1, 152 with Worksheet 2, etc.
My current code seems to only work on the final worksheet, skipping over the first worksheets and hiding only last one. Any help on how I can alter the code to make it work on all the worksheets would be appreciated.
Sub HideSheets()
Dim ws As Worksheet
Dim sWord As String
sWord = Sheets("Autofill").Range("B151").Value
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Visible = True
Next
If sWord = "No" Then
Sheets("Attachment A").Visible = False
End If
sWord = Sheets("Autofill").Range("B152").Value
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Visible = True
Next
If sWord = "No" Then
Sheets("Attachment B Everify").Visible = False
End If
sWord = Sheets("Autofill").Range("B153").Value
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Visible = True
Next
If sWord = "No" Then
Sheets("Attachment C Addendum Acknowle").Visible = False
End If
Application.ScreenUpdating = True
End Sub
Bookmarks