VBA code to export specific variably named tabs to new workbook?

[thebigejr]

Hi all, I've referenced Mr. Excel many times through the years but finally have a need I haven't been able to locate a solve for, so I joined and this is my first post. Any help is greatly appreciated.

Background
I created a "builder file" workbook we run each month. The builder file is macro enabled and contains 13 total sheets. Two sheets are 'behind-the-scenes' sheets which serve to dump data into in order to calculate a 'Top 10 Spend Summary' sheet and 10 individual spend sheets (one sheet for each of the top 10 spenders). After dumping all employee personnel data and month's financial data, I created a macro called 'Calculate' for the user to click (form control button) to trigger the file to calculate. After the workbook calculates, I wrote another macro ('Name Tabs' form control button) to rename the 1 through 10 spend sheets to include the employee's name on each sheet (i.e., "1 - Tom Brady", "2 - Patrick Mahomes", etc.). The macro references a formula I wrote in cell AA2 on each sheet to identify the employee's name that spend data is for. The employee's name is different each month.

What I am looking to do is to write a macro to export the 'Top 10 Spend Summary' tab and the 1 through 10 spend sheets only into a new xlsx workbook (this too will be triggered by a form control button for the user to press after executing the 'Name Tabs' macro). The code I created is below (note: the 'Top 10 Spend Summary' tab is statically named; tabs 1 through 10 will always contain the spender rank (1-10) and a dash (i.e., "1 - ", "2 - ", etc.). I receive a "Run-time error '9': Subscript out of range". I haven't been able to figure out how to write the array for if a sheet name 'contains' type of code for the dynamically named 1-10 sheets (after running the 'Name Tabs' macro). I've tried numerous formats ... "1*", "1 - *", etc..

Ask
Would someone please help me to write a code to account for the variability of the 1 through 10 tabs? Thanks so much.

Sub ExportTabs()

Sheets(Array("Top 10 Spend Summary", "1*", "2*", "3*", "4*", "5*", "6*", _
"7*", "8*", "9*", "10*")).Select
Sheets("10*").Activate
Sheets(Array("Top 10 Spend Summary", "1*", "2*", "3*", "4*", "5*", "6*", _
"7*", "8*", "9*", "10*")).Copy
Sheets(Array("Top 10 Spend Summary", "1*", "2*", "3*", "4*", "5*", "6*", _
"7*", "8*", "9*", "10*")).Select
Sheets("Top 10 Spend Summary").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H5").Select
Application.CutCopyMode = False
Sheets("Top 10 Spend Summary").Select
End Sub

 

[rlv01]

Perhaps something like this.

Sub ExportTabs()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim I As Long
    Dim WSArr() As Variant
  
    Set WB = ActiveWorkbook

    ReDim WSArr(1 To WB.Worksheets.Count)
    For Each WS In WB.Worksheets
        If WS.Name = "Top 10 Spend Summary" Or WS.Name Like "# -*" Then
            I = I + 1
            WSArr(I) = WS.Name
        End If
    Next WS
  
    If I > 0 Then
        ReDim Preserve WSArr(1 To I)
        Sheets(WSArr).Copy                      'copy sheets to new workbook
    Else
        MsgBox "Required worksheets not found", vbCritical, WB.Name
    End If
End Sub

 

[thebigejr]

Perhaps something like this.

Sub ExportTabs()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim I As Long
    Dim WSArr() As Variant
 
    Set WB = ActiveWorkbook

    ReDim WSArr(1 To WB.Worksheets.Count)
    For Each WS In WB.Worksheets
        If WS.Name = "Top 10 Spend Summary" Or WS.Name Like "# -*" Then
            I = I + 1
            WSArr(I) = WS.Name
        End If
    Next WS
 
    If I > 0 Then
        ReDim Preserve WSArr(1 To I)
        Sheets(WSArr).Copy                      'copy sheets to new workbook
    Else
        MsgBox "Required worksheets not found", vbCritical, WB.Name
    End If
End Sub

That worked, Thank you so much!