Solved: Hiding 120 tabs

[acruthi]

I need to hide 120 worksheets in excel at all times and only show 2 of the 120 tabs when a particular selection is made.

I have the below code but just after 22 worksheets data I've reached the max code limit. Is there a better way to do it??? :dunno

Select Case Worksheets("Input").Range("C3").Value
Case "Argentina"
Worksheets("Argentina Market Profile").Visible = True
Worksheets("Argentina Market Profile Detail").Visible = True
Worksheets("Australia Market Profile").Visible = False
Worksheets("Australia Market Profile Detail").Visible = False
Worksheets("China Market Profile").Visible = False
Worksheets("China Market Profile Detail").Visible = False

Case "Australia"
Worksheets("Argentina Market Profile").Visible = False
Worksheets("Argentina Market Profile Detail").Visible = False
Worksheets("Australia Market Profile").Visible = True
Worksheets("Australia Market Profile Detail").Visible = True
Worksheets("China Market Profile").Visible = False
Worksheets("China Market Profile Detail").Visible = False


End Select
End Sub

[Bob Phillips]

Select Case ActiveCell 'Worksheets("Input").Range("C3").Value
Case "Argentina"
Call SetHidden(True, True, False, False, False, False)

Case "Australia"
Call SetHidden(False, False, True, True, False, False)
'etc.

End Select
End Sub

Private Function SetHidden(ParamArray Status())

Worksheets("Argentina Market Profile").Visible = Status(0)
Worksheets("Argentina Market Profile Detail").Visible = Status(1)
Worksheets("Australia Market Profile").Visible = Status(2)
Worksheets("Australia Market Profile Detail").Visible = Status(3)
Worksheets("China Market Profile").Visible = Status(4)
Worksheets("China Market Profile Detail").Visible = Status(5)

End Function

[acruthi]

Thanks :)

What does this function do - Private Function SetHidden(ParamArray Status())
As I mentioned earlier I have about 120 tabs, so do I need to do this for all of them?

Many thanks.

[Sean.DiSanti]

you can just do a loop...

[JKwan]

I would do this another way, by default make all of them to be hiddened. Now, check your cell value and then set the appropriate sheet to be visible.

This should give you a good starting point

Sub HideAllSheets()
Dim WB As Workbook
Dim Index As Long

Set WB = ThisWorkbook
For Index = 1 To WB.Worksheets.Count - 1
WB.Worksheets(Index).Visible = False
Next

Set WB = Nothing
End Sub
Sub UnhideSheet()
Dim WB As Workbook
Dim WS As Worksheet

Set WB = ThisWorkbook
For Each WS In WB.Sheets
If UCase(WS.Name) = UCase(Cells(1, "A")) Then
WS.Visible = True
End If
Next

Set WB = Nothing
Set WS = Nothing
End Sub

[p45cal]

Sub HideShowSheets()
Country = Worksheets("Input").Range("C3").Value
For Each ws In ThisWorkbook.Worksheets
If InStr(1, ws.Name, Country, vbTextCompare) = 0 And UCase(ws.Name) <> "INPUT" Then
ws.Visible = False
Else
ws.Visible = True
End If
Next ws
End Sub
remove And UCase(ws.Name) <> "INPUT" if you don't want to see the Input sheet either.
If you want to hide chart sheets too, remove Work from the code

[acruthi]

Thanks Everyone. All your responses really helped.