Hide multiple sheets, simplify the code [VBA]

[Martin_H]

Hi,

would it be possible to simplify & speed up this code?

Thank you.

Sub HIDE_ALL()

With ThisWorkbook
.Unprotect password:="1111"

If Worksheets("WB_1").Visible = True Then
Worksheets("WB_1").Visible = False
End If

If Worksheets("WB_2").Visible = True Then
Worksheets("WB_2").Visible = False
End If

If Worksheets("WB_3").Visible = True Then
Worksheets("WB_3").Visible = False
End If

If Worksheets("WB_4").Visible = True Then
Worksheets("WB_4").Visible = False
End If

If Worksheets("WB_5").Visible = True Then
Worksheets("WB_5").Visible = False
End If

If Worksheets("WB_6").Visible = True Then
Worksheets("WB_6").Visible = False
End If

If Worksheets("WB_7").Visible = True Then
Worksheets("WB_7").Visible = False
End If

.protect password:="1111"
End With

End Sub

 

[Martin_H]

Any other changes?

Nope, just the worksheet CALC.

 

[Fluff]

Ok, how about

Sub HIDE_ALL()
   Dim Ws As Worksheet
   With ThisWorkbook
      .Unprotect Password:="1111"
      For Each Ws In .Worksheets
         If Ws.Name Like "WB_*" Or Ws.Name = "Calc" Then Ws.Visible = xlSheetHidden
      Next Ws
      .Protect Password:="1111"
   End With
End Sub

 

[Martin_H]

Ok, how about

Sub HIDE_ALL()
   Dim Ws As Worksheet
   With ThisWorkbook
      .Unprotect Password:="1111"
      For Each Ws In .Worksheets
         If Ws.Name Like "WB_*" Or Ws.Name = "Calc" Then Ws.Visible = xlSheetHidden
      Next Ws
      .Protect Password:="1111"
   End With
End Sub

Works perfectly.

Thank you Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[hrayani]

There will always be at least 8 worksheets starting from WB_1 up to WB_7 AND WB_0 which should not be hidden at all.

Just a bit of addition in the master code by @Fluff so that it does not hide sheet "WB_0"

[/B]
Sub HIDE_ALL()
   Dim Ws As Worksheet
   With ThisWorkbook
      .Unprotect Password:="1111"
      For Each Ws In .Worksheets
         If Ws.Name <> "WB_0" And Ws.Name Like "WB_*" Or Ws.Name = "Calc" Then Ws.Visible = xlSheetHidden
      Next Ws
      .Protect Password:="1111"
   End With
End Sub
[B]

 

[Martin_H]

Just a bit of addition in the master code by @Fluff so that it does not hide sheet "WB_0"

[/B]
Sub HIDE_ALL()
   Dim Ws As Worksheet
   With ThisWorkbook
      .Unprotect Password:="1111"
      For Each Ws In .Worksheets
         If Ws.Name <> "WB_0" And Ws.Name Like "WB_*" Or Ws.Name = "Calc" Then Ws.Visible = xlSheetHidden
      Next Ws
      .Protect Password:="1111"
   End With
End Sub
[B]

Thank you Hrayani.

Works perfectly.

I appreciate it.

 

[hrayani]

Thank you Hrayani.

Works perfectly.

I appreciate it.

Your’e Welcome