Forum Discussion
How to hide a number of worksheets using VBA
Dear All, I have a workbook containing a large number of worksheets. One worksheet is a list of all employees (Past and Present) this worksheet is defined under Name Manager as ActiveWorkers ...
Your main problem was not using the .Value property of c.Offset(0,9) when trying to set the visibility property of the worksheet.
Besides fixing that, I also revised the code so it would be case insensitive when testing for YES and NO, and to use the recommended Enum values xlSheetVisible and xlSheetHidden rather than True and False.
Sub HideNonWorkers()
Dim c As Range
For Each c In Range("ActiveWorkers")
If UCase(c.Offset(0, 8).Value) = "NO" Then
ActiveWorkbook.Sheets(c.Offset(0, 9).Value).Visible = xlSheetHidden
ElseIf UCase(c.Offset(0, 8).Value) = "YES" Then
ActiveWorkbook.Sheets(c.Offset(0, 9).Value).Visible = xlSheetVisible
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub