I have some Workbook_Open code that hides all tabs irrelevant to the user based on previously entered data. (i.e. The first time the user opens the template they answer questions and they are shown only the worksheets relevant to their needs. In subsequent openings of the file, the Workbook_Open code ensures that only those relevant worksheets are visible.) Sample code below:

Sub ShowHideTabs ()
        For Each ws In Worksheets
            Select Case ws.CodeName
                Case "Sheet19"
                    ws.Visible = True
                    GoTo Skip
                Case Else
                    ws.Visible = False
            End Select
Skip:
        Next ws
End Sub
What I am trying to do is ensure that if the user creates a new blank worksheet that it does not "disappear" on them upon future openings of the file (you can imagine how frustrating that could be to a user thinking that their work disappeared). The only way I can think of doing this is to use a Workbook_NewSheet event that places the CodeName of any new sheets into a dynamic array (if the sheet is deleted it would be deleted from the array). I would then add a check to the ShowHideTabs sub above so that the array of new sheets are not hidden.

The problem is, I cannot figure out how to create a dynamic array of worksheets where I can write code that will add new worksheets and remove worksheets in the array that are deleted by the user. Any thoughts?