Collapse/Expand Sheets

[Bkisley]

I found a code awhile back that allowed for me to click a tab/sheet name and it would automatically unhide tabs. Then when clicked the tab again, it would hide those same tabs/sheets. What I am looking for is a VBA code that will allow me to click a tab/sheet name called "Show My Clients" and then it would automatically unhide all tabs/sheets to the right of my other tab called "Financial Dashboard". The "Show My Clients" tab name will then be renamed to "Hide My Clients". Then when I click on the "Hide My Clients" tab/sheet name, it will hide those same tabs/sheets and will rename the tab/sheet back to "Show My Clients".

The old code I found below that did something similar to what I am looking for is below. My issue with this code is that it hides/unhides too much. It doesn't hide my specific range of sheets I need it to hide. If it helps the code, I have a tab/sheet called "Keep @ End" that is always my last tab/sheet in my file that is used for a different code. So What I need this code to do is hide/unhide all tabs between "Financial Dashboard" and Keep @ End"

Private Sub Worksheet_Activate()
    Dim sheet As Worksheet
    Application.ScreenUpdating = False
    If ShowHide.Name = "Show My Clients" Then
        'Make all sheets visible
        For Each sheet In ThisWorkbook.Sheets
            sheet.Visible = xlSheetVisible
        Next sheet
        'Change the sheet name to the "Collapse" name you want
        ShowHide.Name = "Hide My Clients"
        'Pick a sheet to display after the once hidden sheets are expanded
        Sheet4.Activate
    Else
        'Hide all sheets except the one's you want to keep visible
        For Each sheet In ThisWorkbook.Sheets
            If (sheet.Name <> Results.Name And sheet.Name <> Run.Name And sheet.Name <> ShowHide.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet
        'Change the sheet name to the "Expand" name you want
        ShowHide.Name = "Show My Clients"
        'Pick a sheet to display after the sheets to be hidden are collapsed
        Run.Activate
    End If
    Application.ScreenUpdating = True
End Sub









Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Fluff]

What are the codenames for the dashboard & End sheets?

 

[Bkisley]

I actually found a work around to anyone else that may run into wanting to do something lie this
I ended up using a code that hides/unhides sheets based on their color. So all my client tabs will be one color and when my toggle sheet name is clicked, all tabs colored in the standard light green color will hide/unhide. Consider this closed on my end!

 

[Fluff]

Glad you sorted it & thanks for the feedback.