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.