How to automatically hide worksheets?

[graduate106]

I have a master sheet and on it is a drop down box with the numbers 1 to 10.

I then have 10 worksheets (named 1,2,3,4...10) in the same workbook and i want to automatically show/hide the correct amount of worksheets.

Eg. If i select number 5 from the drop down box on the summary sheet i want it to show the first 5 worksheets (and hide the other ones as i dont want them). When i change the number to 6 i want it to make the 6th one appear etc.

I am sure this is possible but after lots of forum searches i am still puzzled!

Many thanks

 

[VoG]

Try this: right click the master sheet's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Address(False, False) = "A1" Then
    For i = 1 To Target.Value
        Sheets(CStr(i)).Visible = True
    Next i
    If i = 10 Then Exit Sub
    For i = Target.Value + 1 To 10
        Sheets(CStr(i)).Visible = False
    Next i
End If
End Sub

Change A1 to the cell with the drop down.

 

[graduate106]

Amazing thanks VOG that works perfect!

First time I've actually posted on this forum so i'll definitely be coming back

Cheers

 

[graduate106]

Going one step further, is it possible to choose this number (so say 6 tabs are visable) then the tabs are named by a box on the summary tab?

For example i would choose 6 departments on summary sheet then 6 tabs would be visable then i could specify the departments again on the summary sheet and the tabs would be named accordingly eg. Accounts, Sales, etc. (instead of just 1,2,3,4 like they are now)

Help is very much appreciated

Thanks

 

[VoG]

Deleted erroneous answer.

 

[VoG]

The problem here is that the code expects the sheets to be named 1, 2 and so on. If you rename those sheets, the code will fail the next time it is run.

What is the order of the sheets looking at the tabs from left to right? Is it Master, 1,2, ...

 

[graduate106]

Thanks, i want the names to be displayed on the summary tab to be honest though.

eg. A1 = drop down box with 1-10 then
B1 to B10 being boxes for me to enter the names of the tabs. i already have a macro to only show say lines 1-5 if i select 5 from the drop down box (ie. hiding 6-10) so i just want to be able to enter the names of the tabs in column B then them to link through onto the tab names.

Is that possible?


That macro you gave is really nifty though - i will certainly use that in future

 

[graduate106]

And yes sorry, the order of the sheets is Master, then 1,2,3,4,....10

Thanks again

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Address(False, False) = "A1" Then
    For i = 2 To Target.Value
        Sheets(i).Visible = True
        Sheets(i).Name = Range("B" & i - 1).Value
    Next i
    If i = 10 Then Exit Sub
    For i = Target.Value + 2 To 11
        Sheets(i).Visible = False
    Next i
End If
End Sub

 

[graduate106]

ALMOST perfect!

When i select 1 it works fine
When i select 2 it only displays 1
When i select 3 it only displays 2
and so on....
When i select 10 it only displays 9

Small amendment needed somewhere?

Thanks again