Hiding sheets using code

[Roger on Excel]

i have a spreadsheet with sheets named as follows

St1, St2, St3, St4, St5 etc...

I would like to enter a number into a cell on a separate sheet that will
result in
only showing the sheets up to that number and hide the rest.

For example if i enter 3, I would like St1, St2 and St3 to show and St4, St5
etc
to be hidden.

Can anyone help?

 

[Bob Phillips]

For i = 1 to Range("B2").value
Worksheets("St" & i).Visible = xlSheetHidden
Next i

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Roger,

Right click your 'other' shhet tabe (Menu in my case) view code and oaste
this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
For i = 1 To worksheets.Count
If worksheets(i).Name <> "Menu" And Val(Right(worksheets(i).Name,
1)) > Target.Value Then
worksheets(i).Visible = False
Else
worksheets(i).Visible = True
End If
Next
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Enter a number in A1 of that sheet and sheets will become hidden or visible

Mike

 

[Roger on Excel]

Excellent - Thanks very much - perfect !!!


All the best,

Roger

 

[Roger on Excel]

By the way I have sheets named St10, St11, St12, yet these do not disappear -
is there a modification to the code to take account of these sheets also?