Hide sheets

[Steve]

Hi all. I have a list of sheets within the workbook in the range
Sheet1 A1:A10. How can I have vba hide the sheets that are listed in
that range? Thanks!

 

[Mike]

Sub testData()
Dim whatSheet As Worksheet
For i = 1 To 10
Set curCell = Worksheets("Sheet1").Cells(i, 1)
If Not IsEmpty(curCell.Value) Then
Set whatSheet = Worksheets(curCell.Value)
whatSheet.Visible = xlSheetVeryHidden
End If
Next i
End Sub

 

[Michael]

Try something like this:
Sub Test()
Dim sht As Worksheet

Dim rng As Range
Dim myshtnames As Range

Set sht = Worksheets("Sheet1")
Set myshtnames = sht.Range("A1:A10")


For Each rng In myshtnames
shtnam = rng.Value
Worksheets(shtnam).Visible = False
Next

End Sub





--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Charlie]

Dim Cell As Range

On Error Resume Next
For Each Cell In Range("A1:A10")
Sheets(Cell.Value).Visible = xlSheetHidden
Next Cell

' or you can use:
' Sheet1.Visible = xlSheetVeryHidden

 

[Kevin B]

I named the range of cells containing the worksheet names "MyList" and used
the following code:

Sub HideAway()

Dim w As Worksheet
Dim r As Range
Dim i As Integer
Dim strSheet As String

Set r = Range("MyList")

For i = 1 To r.Cells.Count
strSheet = r.Cells(i)
For Each w In ThisWorkbook.Worksheets
If w.Name = strSheet Then
w.Visible = xlSheetHidden
End If
Next w
Next i

Set w = Nothing
Set r = Nothing

End Sub

 

[Charlie]

I meant to say, or you can use:

Sheets(Cell.Value).Visible = xlSheetVeryHidden

....depending on whether or not you want your users to be able to unhide them
via Format menu.

 

[Rick Rothstein \(MVP - VB\)]

Something like this maybe...

Sub HideSheets()
Dim X As Long
On Error Resume Next
For X = 1 To 10
If Len(Cells(X, "A").Value) > 0 Then
Worksheets(Cells(X, "A").Value).Visible = False
End If
Next
End Sub

Note: The On Error Resume Next is to protect against a sheet name that does
not exist.

Rick