Open specific worksheet and hide all other worksheet

[elsuji]

Dear Team,
I need a code for open specific worksheet and hide all other worksheets.

Example,

If i am having sheet 1, sheet 2, sheet 3, sheet 4, sheet 5, sheet 6.

I am in sheet 1. I want to open sheet 3. If i click command button, sheet 3 only visible and hide all other sheets (sheet 1, sheet 2, sheet 4, sheet 5, sheet 6)

And from sheet 3 if i want open sheet 6. So if i click the command button, sheet 6 only visible and Sheet 3 should he hidden.

I want only one sheet should visible always. All other sheets are hidden.

Can any one please help me how to do this

[elsuji]

Can any one please reply me

[jolivanes]

Show us what you have tried yourself. After all, hiding a sheet is not rocket science.
I know that these forums are to help people but if people don't help themselves by trying they'll never learn, even if some one gives them the answer.

[Paul_Hossler]

The logic isn't that hard, so this will get you started




Option Explicit


Sub HideSheets()
Select Case ActiveSheet.Name
Case "Sheet1"
Call HideAllExcept("Sheet2")
Case "Sheet2"
Call HideAllExcept("Sheet3")
Case "Sheet3"
Call HideAllExcept("Sheet4")
Case "Sheet4"
Call HideAllExcept("Sheet5")
Case "Sheet5"
Call HideAllExcept("Sheet6")
Case "Sheet6"
Call HideAllExcept("Sheet1")
End Select
End Sub


Private Sub HideAllExcept(s As String)
Dim ws As Worksheet

Application.ScreenUpdating = False


Worksheets(s).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> s Then ws.Visible = xlSheetHidden
Next


Application.ScreenUpdating = False
End Sub

[jolivanes]

I had the idea that it was wanted probably because of many worksheets in a workbook and therefor unwieldy to select sheets.
If you have a UserForm with a ListBox ("ListBox1"), you would not have to put all the sheet names in the code.
But whatever, he/she has a couple choices.

Private Sub UserForm_Initialize()
Dim sh As Worksheet, shArr
For Each sh In Sheets
shArr = shArr & sh.Name & "|"
Next
ListBox1.List = WorksheetFunction.Transpose(Split(shArr, "|"))
End Sub



Private Sub ListBox1_Click()
Dim a As String, sh As Worksheet
a = ListBox1
Unload Me
With Sheets(a)
.Visible = True
.Select
End With
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name And sh.Visible = True Then sh.Visible = False: Exit Sub
Next sh
End Sub

[jolivanes]

Forgot to attach.

[elsuji]

Dear Paul,

This what exactly i want. Thanks for your reply.