CODEWITHSUNDEEP
vbaexcel
Michi
Michi

Reputation: 5471

Loop to hide sheets

Currently, I am using this VBA code to hide all sheets in my spreadsheet:

Sub HideSheets()
    Sheet1.Visible = False
    Sheet2.Visible = False
    Sheet3.Visible = False
    Sheet4.Visible = True
End Sub

This code runs perfectly.

However, since I have more than just 4 sheets in my original file I would like to have a solution with a loop. Therefore, I tried to go with the following formula:

Sub LoopHideSheets()
    Dim b As Worksheet
    For Each b In Worksheets
        b.Select
        ActiveWindow.Visible = False
    Next b
End Sub

Once I run this code my Excel file crashes. I guess the reason for this is that at least one file needs to stay visible. Do you know what I have to change my loop code so all sheets getting hidden instead of Sheet4?

Upvotes: 1

Views: 1844

Answers (4)

CLR
CLR

Reputation: 12254

This will hide every sheet that is not named "Sheet4" - but be careful, you need to ensure Sheet4 exists or you will get an error.

Sub LoopHideSheets()

Dim b As Worksheet

For Each b In Worksheets
    If b.Name <> "Sheet4" Then b.Visible = False
Next b

End Sub

You might want to hide all sheets other than the one currently active..?

    If b.Name <> ActiveSheet.Name Then b.Visible = False

However, you may need to hide all but 1 (hey, I've no idea why) as per other answers. To do this properly, you need to count visible sheets and only deal with those:

Sub LoopHideSheets()

Dim b As Worksheet, shtcnt As Long

'Count up all visible sheets
For Each b In Worksheets
    If b.Visible = True Then shtcnt = shtcnt + 1
Next b

'Hide each visible sheet until only 1 is left
For Each b In Worksheets
    If b.Visible = True And shtcnt > 1 Then
    shtcnt = shtcnt - 1
    b.Visible = False
    End If
Next b

End Sub

Upvotes: 1

Sphinx
Sphinx

Reputation: 660

If You always want to have last sheet visible you could use this

Sub HideSheets()

Dim i As Long

With ThisWorkbook
    For i = 1 To .Sheets.Count - 1
        .Sheets(i).Visible = False
    Next i
End With

End Sub

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Alternatively you can catch the error with error handling

Sub HideAllSheets()
    Dim b As Worksheet
    For Each b In Worksheets
        On Error Resume Next 'disable error reporting
        b.Visible = False
        If Err.Number = 1004 Then
            MsgBox "The last sheet must stay visible" 'remove if you don't want a message
            Exit Sub
        End If
        On Error GoTo 0 're-enable error handling. Don't forget this line!
    Next b
End Sub

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6984

Sub LoopHideSheets()
    Dim b As Worksheet
    For Each b In Worksheets
        If b.Name <> "DontHide" Then    'whatever the sheet name is to not hide
            b.Visible = False
        End If
    Next b
End Sub

Upvotes: 1

Related Questions