Hiding sheets using code

R

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?
 
B

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)
 
M

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
 
R

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top