VBA hiding sheets except for one

[asad]

Hello All,

I got this macro from this forum and it is working alright except for one little issue. When I run it, it hides all sheets (as it should) except for "Master" (again as it should), but it also leaves one last sheet visible. Why is that?

Sub HideMultiSheets()
Dim myRange As Range
Dim cell As Range
    Set myRange = Sheets("Master").Range("A2:A60")
    
    On Error Resume Next
    For Each cell In myRange
        If cell.Value <> "" Then
            Sheets(cell.Value).Visible = False
        End If
    Next cell
    Sheets("Master").Visible = True
    On Error GoTo 0
End Sub

 

[Peter_SSs]

We cannot see your Master sheet values or know what your sheet names are but there are 2 possible causes that come to my mind.

1. The extra sheet that remains visible is not in the list in the range A2:A60 of 'Master'

2. The more likely reason. The code is trying to hide all the sheets and then unhide 'Master'. However, a workbook cannot have all sheets hidden, so when it get to the last one an error occurs and the sheet is not hidden. The code says to keep going even if an error occures (On Error Resume Next) so it continues on and eventually unhides 'Master'

There are several options, one of which would be to first ensure 'Master' is visible and then exclude hiding it as you work through the list.

Sub HideMultiSheets()
Dim myRange As Range
Dim cell As Range
    Set myRange = Sheets("Master").Range("A2:A60")
    Sheets("Master").Visible = True
    On Error Resume Next
    For Each cell In myRange
        If cell.Value <> "" And cell.Value <> "Master" Then
            Sheets(cell.Value).Visible = False
        End If
    Next cell
    On Error GoTo 0
End Sub

 

[asad]

We cannot see your Master sheet values or know what your sheet names are but there are 2 possible causes that come to my mind.

1. The extra sheet that remains visible is not in the list in the range A2:A60 of 'Master'

2. The more likely reason. The code is trying to hide all the sheets and then unhide 'Master'. However, a workbook cannot have all sheets hidden, so when it get to the last one an error occurs and the sheet is not hidden. The code says to keep going even if an error occures (On Error Resume Next) so it continues on and eventually unhides 'Master'

There are several options, one of which would be to first ensure 'Master' is visible and then exclude hiding it as you work through the list.

Sub HideMultiSheets()
Dim myRange As Range
Dim cell As Range
    Set myRange = Sheets("Master").Range("A2:A60")
    Sheets("Master").Visible = True
    On Error Resume Next
    For Each cell In myRange
        If cell.Value <> "" And cell.Value <> "Master" Then
            Sheets(cell.Value).Visible = False
        End If
    Next cell
    On Error GoTo 0
End Sub

Brilliant.
Thanks a lot Peter. It was the second case as I was trying to hide all the sheets and then unhide Master. But your code does the perfect job.
Thanks

Asad

 

[Peter_SSs]

Brilliant.
Thanks a lot Peter. It was the second case as I was trying to hide all the sheets and then unhide Master. But your code does the perfect job.
Thanks

Asad

Glad it helped.

 

[Rick Rothstein]

I think this macro should also work...

[table="width: 500"]
[tr]
	[td]Sub HideMultiSheets()
  Sheets("Master").Visible = True
  Sheets(Split(Replace(Replace(Application.Trim(Join([TRANSPOSE(SUBSTITUTE(MASTER!A2:A60," ","|"))])), " ", "*"), "|", " "), "*")).Visible = False
End Sub[/td]
[/tr]
[/table]

 

[asad]

I think this macro should also work...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub HideMultiSheets()
  Sheets("Master").Visible = True
  Sheets(Split(Replace(Replace(Application.Trim(Join([TRANSPOSE(SUBSTITUTE(MASTER!A2:A60," ","|"))])), " ", "*"), "|", " "), "*")).Visible = False
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Sorry for the late reply Rick.

This probably will work but I cannot even understand this
Wayyyyy beyond my little brain's ability.

But thanks for your time and another possible solution.
Appreciate your time and work.
Thanks
Asad