How can I create a dynamic array of sheets based on a partial sheet name criteria?

user_18701837 0
Viewed 42

I have a large Excel workbook generating accounting bridges for multiple entities and each entity has a tab called "[Entity name] Bridge". I want to be able to create a code that will capture all the "[Entity name] Bridge" tabs in a dynamic array to be used in code that will apply to all. The tricky part is that I want it to remain dynamic if I add/remove entites in the document at a later date e.g. entity count moves from 12 to 13 = array increases in size accordingly.

I know how to create the static array using

For Each ws In ThisWorkbook.Sheets(Array("[Entity name] BRIDGE", "[Entity Name2] Bridge", ... , "[Entity NameN] Bridge"))

but ideally I could use some code that will add the extra Bridge sheet to the array by searching for all tab names with " Bridge" in the name and then collecting them.

Sorry if I'm missing something obvious!

dynamic vba excel arrays
1 Answers

Michael. I would use this code which looks for every sheet of the workbook and then checks their names if they contain "BRIDGE" at the end of the name (check the "*"). It will consider spaces, that means if you write "* BRIDGES " (space at the end) it won't work. the * means "contain".

For this code doesn't matter the amount of sheets, and also you don't need to write a list of names.

Sub arrListSheets()

Dim ws              As Worksheet
Dim wsName          As String
Dim arrSheetsName() As Variant
Dim SheetNum        As Double, X As Double

X = 1
For Each ws In ThisWorkbook.Sheets
    wsName = UCase(ws.Name)
    If wsName Like "* BRIDGE" Then
      'Here you add something else what you want to do with the sheets.
        ReDim Preserve arrSheetsName(1 To 2, 1 To X)
        arrSheetsName(1, X) = X
        arrSheetsName(2, X) = ws.Name
        X = X + 1
    End If
Next ws

arrSheetsName = Application.WorksheetFunction.Transpose(arrSheetsName)

End Sub
user_143159260
Related