How to hide a number of worksheets using VBA

[vonryan]

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 (='Labour Total'!$A:$J).

I have a button on this worksheet that should hide all worksheets of employees that are no longer working. This is to minimise the number of tabs at the bottom of the active workbook.

The VBA I have used is detailed below.

Sub HideNonWorkers()

Dim c As Range

For Each c In Range("ActiveWorkers")
If (c.Offset(0, 8) = "NO") Then
ActiveWorkbook.Sheets(c.Offset(0, 9)).Visible = False
ElseIf (c.Offset(0, 8) = "YES") Then
ActiveWorkbook.Sheets(c.Offset(0, 9)).Visible = True
End If
Next
On Error GoTo 0
Application.EnableEvents = True

End Sub

I get an error message when th code runs.

In diagnostics it tells me the the return values of the code, which appears correct. I do not know where the error is.

The code looks at the active worksheet and evaluates the value of column I of the worksheet. If the value is "NO" then the code gets the worksheet name from column J and is supposed to set the named worksheet to not visible.

Can anyone help please.

Best Regards

Vonryan

[#2]

@vonryan 

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