Automatically hiding/unhiding 'sub' worksheets when 'master' worksheet is selected

Question

Problem

I'm running a macro which results in several new worksheets being created, based on user input; at least one 'master' worksheet, and several 'sub' worksheets. These will have a format along the lines of "Group 1 Master", "Group 1 Sub 1", "Group 2 Sub 2", "Group 2 Sub 3" ....... "Group n Master", "Group n Sub 1"......

I would like to find a way to hide the sub sheets until their master sheet is selected/activated, at which point they should become visible, and then when the master sheet isn't selected, they should be hidden again.

Approach

I've found sources indicating how to manually achieve this, by inserting something like the sub below into the given worksheet, however my sheets are being generated by a macro, and I don't want to manually go through and add this to all of them.

Private Sub Worksheet_Activate()
    For Each SubSheet in SubSheets
        SubSheet.Visible = False
    Next SubSheet
End Sub

Question

How do I hide all sub sheets apart from when their master is selected?

Thanks

Answer 1

Imagine this is how it looks with all sheets visible:

Then this is how it looks with all sub sheets hidden:

And this is how it works with auto hide/show the sub sheets for the active master sheet:

I used the following codes:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    'find master left of selected sheet
    Dim ActiveMaster As Object
    Dim iSht As Long
    For iSht = Sh.Index To 1 Step -1    'loop backwards until a master is found
        If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") > 0 Then
            Set ActiveMaster = ThisWorkbook.Sheets(iSht)
            Exit For
        End If
    Next iSht

    If ActiveMaster Is Nothing Then
        'no master sheet left of current sheet found
        MsgBox "No 'Master' sheet found.", vbCritical
        Exit Sub
    End If

    'find last sub sheet
    Dim LastSub As Object
    For iSht = ActiveMaster.Index + 1 To ThisWorkbook.Sheets.Count
        If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") > 0 Then
            Set LastSub = ThisWorkbook.Sheets(iSht - 1)
            Exit For
        End If
    Next iSht

    'if no last sub sheet was found it is the very last sheet
    If LastSub Is Nothing Then
        Set LastSub = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    End If

    'hide all SUB-sheets left of master sheet and right of LastSub sheet
    If ActiveMaster.Index > 1 Then
        For iSht = 1 To ThisWorkbook.Sheets.Count
            If iSht < ActiveMaster.Index Or iSht > LastSub.Index Then
                If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") = 0 Then
                    ThisWorkbook.Sheets(iSht).Visible = xlSheetHidden
                End If
            Else
                ThisWorkbook.Sheets(iSht).Visible = xlSheetVisible
            End If
        Next iSht
    End If
End Sub


Public Sub ShowAllSheets()
    Dim iSht As Long
    For iSht = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.Sheets(iSht).Visible = xlSheetVisible
    Next iSht
End Sub


Public Sub HideAllSubSheets()
    Dim iSht As Long
    For iSht = 1 To ThisWorkbook.Sheets.Count
        If InStr(1, ThisWorkbook.Sheets(iSht).Name, "Master") = 0 Then
            ThisWorkbook.Sheets(iSht).Visible = xlSheetHidden
        End If
    Next iSht
End Sub