Can't pass string into array function : subscript out of range

Question

I'm trying to imitate copying multiple sheets to a new workbook and this is fine if I literally use the sheet names in the array function.

However if I try to pass a string variable into the array I get a subscript out of range error.

The line of concern is:

Wb.Sheets(Array(SheetsArray)).Copy After:=RegionWb.Sheets(RegionWb.Sheets.count)

Please see my code below :

Sub CreateFiles()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim WKC As String: WKC = Replace(DateValue(DateAdd("ww", -1, Now() - (Weekday(Now(), vbMonday) - 1))), "/", ".")
    Dim FilePath As String: FilePath = "Z:\MI\Krishn\Retail"
    Dim BuyerLastRow As Long
    Dim Wb As Workbook: Set Wb = ActiveWorkbook
    Dim RegionWb As Workbook
    Dim RegionCount As Integer
    Dim RegionCounter As Integer
    Dim SheetsArray As String
        With BuyerList
            LastRow = .Range("G1048576").End(xlUp).Row
            BuyerLastRow = .Range("A1048576").End(xlUp).Row
            'Create WKC Dir

            If Dir(FilePath & "\" & WKC, vbDirectory) = "" Then
                MkDir FilePath & "\" & WKC
            End If

            'Create Create Files
            If CountFiles(FilePath & "\" & WKC) = 0 Then
                For i = 2 To LastRow
                    RegionCounter = 0
                    SheetsArray = ""
'                    Set RegionWb = Workbooks.Add
'                    'wb.SaveAs FilePath & "\" & WKC & "\" & .Cells(i, 7).Value
'                    RegionWb.SaveAs FilePath & "\" & WKC & "\" & "WKC " & WKC & " - " & .Cells(i, 7).Value & ".xlsb", 50

                        For j = 2 To BuyerLastRow
                            RegionCount = Application.WorksheetFunction.CountIf(.Range("C:C"), .Cells(i, 7).Value)
                            If .Cells(i, 7).Value = .Cells(j, 3).Value Then
                                SheetsArray = SheetsArray & """" & .Cells(j, 2).Value & ""","
                                RegionCounter = RegionCounter + 1
                                If RegionCounter = RegionCount Then
                                    Debug.Print Left(SheetsArray, Len(SheetsArray) - 1)
                                    Set RegionWb = Workbooks.Add
                                    RegionWb.SaveAs FilePath & "\" & WKC & "\" & "WKC " & WKC & " - " & .Cells(i, 7).Value & ".xlsb", 50
                                    'Wb.Sheets(Array(Left(SheetsArray, Len(SheetsArray) - 1))).Copy After:=RegionWb.Sheets(RegionWb.Sheets.count)
                                    SheetsArray = Left(SheetsArray, Len(SheetsArray) - 1)
                                    Wb.Sheets(Array(SheetsArray)).Copy After:=RegionWb.Sheets(RegionWb.Sheets.count)
                                    'Wb.Sheets(Array()).Copy After:=RegionWb.Sheets(RegionWb.Sheets.count)
                                    RegionWb.Save
                                    RegionWb.Close
                                    Exit For
                                End If
'                                Wb.Sheets(Wb.Sheets("Buyer list").Range(Cells(j, 2).Address).Value).Copy After:=RegionWb.Sheets(RegionWb.Sheets.count)
                            End If
                        Next j
'
'

                Next i
            End If


        End With
    Application.ScreenUpdating = True
End Sub

Answer 1

You can split the string into an Array like this:

Wb.Sheets(Split(SheetsArray, ",")).Copy After:=RegionWb.Sheets(RegionWb.Sheets.Count)

As GSerg pointed out: You'll need to remove the quotes around the Worksheet names.

SheetsArray = SheetsArray & .Cells(j, 2).Value & ","

The backslash would be a safer delimiter that using a comma because Worksheet names can include a comma but not a backslash.

SheetsArray = SheetsArray & .Cells(j, 2).Value & "/"

Wb.Sheets(Split(SheetsArray, "/")).Copy After:=RegionWb.Sheets(RegionWb.Sheets.Count)