VBA - Using an array to copy a dynamic range of sheets to a new book

[BigShango]

Hi,

Having a bit of bother getting my head around using arrays.

I have a list of names in Column A of a sheet. I need to copy any sheets in the workbook that match those names to a new workbook.

So, for example, Column A on Sheets("Names") is
Steve Wilson
John Jackson
Percy Peterson
Rab Rabson

In the workbook there are sheets named Percy Peterson and Rab Rabson, so I need them copied to a new workbook, there are not sheets names Steve Wilson or John Jackson.

What I thought I needed to do is create an array for the list of names (NamesArray) then search each one across the whole workbook, if a sheet matches that name add it to another array (CopyArray) but I just can't seem to get it. I really struggle with arrays tbh.

This is also part of a much larger process.

 

[Puertorekinsam]

Try this:

Dim NameArray() As String
Sub MoveBookNames()
    Dim ws As Worksheet
    Dim i As Integer
    i = 1
    ReDim Preserve NameArray(0 To 0) As String
    Do Until Sheets("Names").Cells(i, 1) = ""
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = Sheets("Names").Cells(i, 1) Then
                ReDim Preserve NameArray(0 To UBound(NameArray) + 1)
                NameArray(UBound(NameArray) - 1) = ws.Name
            End If
        
        
        Next
    
    
        i = i + 1
    Loop
    ReDim Preserve NameArray(0 To UBound(NameArray) - 1)
    ThisWorkbook.Sheets(NameArray).Copy

End Sub