hiding sheets

[sedgely]

i have the following code which hides all sheets except the one named

Function hideAll()
Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "MainMenu" Then ws.Visible = xlSheetVeryHidden
Next
End Function

What I want to do is to hide sheets based on a list of sheet names given on sheet1. so if range A1:A10 contains a list of sheet names i want the code to hide those sheets. any pointers on how i can modify my code to make this happen?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[rmikesmith]

Craig,

A very simple version with no error checking:

Function hideAll()
Dim oCell As Range

 For Each oCell In ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
   ThisWorkbook.Worksheets(oCell.Text).Visible = xlSheetVeryHidden
 Next oCell

End Function

Regards,
Mike

 

[firefytr]

Hi Craig,

Try this ...

Function hideAll()
    Dim arrWs(), i As Long
    With ThisWorkbook
        arrWs = .Sheets("Sheet1").Range("A1:A10").Value
        On Error Resume Next 'for bad sheet names or missing array items
        For i = LBound(arrWs) To UBound(arrWs)
            If .Sheets(arrWs(i, 1)).Name <> "MainMenu" Or .Sheets.Count > 1 Then
                .Sheets(arrWs(i, 1)).Visible = xlSheetVeryHidden
            End If
        Next i
    End With
End Function

-----------
Regards,
Zack Barresse

 

[sahmiele]

Try this...
Function hideAll()
Dim ws As Worksheet
c = 0
For Each A In Range("a1:a10")
ws(c) = A.Text
Sheets(ws(c)).Visible = xlSheetVeryHidden
c = c + 1
Next A

 

[sedgely]

Thanx guys
Went with firefytr's solution, saves me adding the error trappin

Cheers, Craig
Si fractum non sit, noli id reficere