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

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 + 1
        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]

Dont worry i've sorted it now

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 + 1).Visible = True
Sheets(i + 1).Name = Range("B" & i).Value
Next i
If i = 11 Then Exit Sub
For i = Target.Value + 2 To 11
Sheets(i).Visible = False
Next i
End If
End Sub


many thanks for your help VOG!

 

[graduate106]

Finally (i promise!)

How do i get the macro to run everytime a cell in B1-10 is changed (ie. when i change B1 to Accounts, B2 to Sales etc. Currently it only runs when i select a number from the list

I change the number first to say 5 then i change the contents of cells B1-B10 after that and the tabs dont change name until i change the number again.

Thanks

 

[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 + 1
        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
ElseIf Not Intersect(Target, Range("B1:B10")) Is Nothing Then
    Sheets(Target.Row + 1).Name = Target.Value
End If
End Sub

 

[graduate106]

Genius! Thank you so much!