Copy Sheets to new workbook using Wildcards

[akash14]

Hello,

I want to copy worksheets from current workbook to a new workbook. The names of Worksheet have format: Name-Month
Ex: Adam-Mar, Adam-Apr, John-Mar, John-Apr.

Now what I am trying to do is copy all Adam sheets to one workbook and all John Worksheets to another.

The code which I currently use only copy one sheet. I don't know how to create an array of all sheets with same name and then copy it. My current code is:

Do While Not IsEmpty(Cells(i, 1))
         save_name = Cells(i, 1)
         sname = Cells(i, 1) & "-" & Sheets("Cal").Cells(3, 4)
         Sheets(sname).Copy
         'saving the file code

save_name is the name of the person (Adam,John). And Sheets("Cal").Cells(3,4) actually had the value "Mar" and it saved all sheets with Name-Mar with the Loop(Since there was only one sheet per name). But now to copy multiple sheets I need to create an array and I have no idea how to do it. Is it possible to copy all sheets with Adam-* to one workbook.

Any help will be appreciated. Thanks

 

[Gavin T]

This would work:

Sub Copy_Sheets()

    Dim wSheet      As Worksheet
    Dim arrJohn()   As String
    Dim arrAdam()   As String
    Dim i           As Integer
    Dim j           As Integer

    
    For Each wSheet In ThisWorkbook.Worksheets
        If wSheet.Name Like "John*" Then
            ReDim Preserve arrJohn(i)
            arrJohn(i) = wSheet.Name
            i = i + 1
        ElseIf wSheet.Name Like "Adam*" Then
            ReDim Preserve arrAdam(j)
            arrAdam(j) = wSheet.Name
            j = j + 1
        End If
    Next wSheet

    With ThisWorkbook
        .Activate
        Sheets(arrJohn).Copy Before:=Workbooks("[COLOR="#FF0000"][B]Book1.xlsx[/B][/COLOR]").Sheets([COLOR="#FF0000"][B]1[/B][/COLOR])
        .Activate
        Sheets(arrAdam).Copy Before:=Workbooks("[COLOR="#FF0000"][B]Book2.xlsx"[/B][/COLOR]).Sheets([COLOR="#FF0000"][B]1[/B][/COLOR])
    End With
End Sub

Remember to change the code colored red to match your specific needs.

 

[akash14]

Thanks Gavin. Works perfectly. I just had to Erase the array everytime because I am using it in a while loop because I have a lot of names and I didn't want to write For loop for each of them.

Thanks again. I really appreciate your help.