Run-time error '1004' on hiding worksheets

[brentfid]

I have this code in

Private Sub Workbook_Open()
ActiveWorkbook.Sheets("Cost&Marg_01").Activate
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Cost&Marg_01" Then
sh.Visible = False
Else
sh.Visible = True
End If
Next
End Sub

I get an applicaton defined or object defined error when it gets to the
sh.Visible=False line.

What have I improperly defined here?

Brent

 

[Norman Jones]

Hi Brent,

At least one sheet must be visible. So, if no sheet named "Cost&Marg_01"
exists, the reported error will occur. Check, therefore, that this sheet
really does exist. Perhaps there is a subtle space or spelling difference.

Providing that the named sheet does exist, your code runs without error for
me and hides all other worksheets.

 

[brentfid]

The sheet "Cost&Marg_01" indeed exists in the file. What else could it
be?

 

[Norman Jones]

Hi Brent,

Try running this version:

Private Sub Workbook_Open()
Dim sh As Worksheet

ThisWorkbook.Sheets("Cost&Marg_01").Visible = True
For Each sh In ThisWorkbook.Worksheets
If UCase(sh.Name) <> UCase("Cost&Marg_01") Then
sh.Visible = False
Else
sh.Visible = True
End If
Next
End Sub