Collapse and Expanding Worksheet Tabs

Hello Nguyen,

The link you are referring to, is not going to help you, because that code there is expanding and collapsing all worksheets except two. The loop is Thisworkbook.sheets 

What you need is to have 12 Months worksheets and then 4 sheets each for a single quarter and then you need 4 separate codes to place in each of those quarter sheets.  In Addition, you need one helper worksheet that has to be there all the time and must not be deleted. that worksheet is needed, in case if you collapse all quarters and there has to be a sheet to be activated in order for VBA to avoid tossing error.

I have written the below code that loops through the array of worksheets for specific quarter like this 

ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar"))

The animation shows how it works.  I have attached the workbook with code embedded to it.

Private Sub Worksheet_Activate()
' This is for first Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar"))


    Application.ScreenUpdating = False
    If ShowHide1.Name = "Show Quarter1" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide1.Name = "Hide Quarter1"
        ShowHide1.Tab.Color = vbYellow

        Sheet1.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide1.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide1.Name = "Show Quarter1"
        ShowHide1.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
' This is for Second Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Apr", "May", "Jun"))


    Application.ScreenUpdating = False
    If ShowHide2.Name = "Show Quarter2" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide2.Name = "Hide Quarter2"
         ShowHide2.Tab.Color = vbYellow

        Sheet6.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide2.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide2.Name = "Show Quarter2"
         ShowHide2.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()

' This is for Third Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Jul", "Aug", "Sep"))


    Application.ScreenUpdating = False
    If ShowHide3.Name = "Show Quarter3" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide3.Name = "Hide Quarter3"
         ShowHide3.Tab.Color = vbYellow

        Sheet10.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide3.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide3.Name = "Show Quarter3"
        ShowHide3.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub


Private Sub Worksheet_Activate()
' This is for forth Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Oct", "Nov", "Dec"))


    Application.ScreenUpdating = False
    If ShowHide4.Name = "Show Quarter4" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide4.Name = "Hide Quarter4"
        ShowHide4.Tab.Color = vbYellow

        Sheet15.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide4.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide4.Name = "Show Quarter4"
        ShowHide4.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub