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

You do not build a comma-separated string to pass to Array, that's not how it works. Declare an array beforehand and fill it without calling Array. — GSerg
– GSerg, Commented Oct 12, 2016 at 12:57
Array(SheetsArray) creates an array with one entry. In Sheets(X), X can be a number or a string but not an array. Try MyArray = Array(SheetsArray) and Sheets(MyArray(0)). Warnings. MyArray must be declared as Variant to be initialised with Array. The lower bound of an array created with Array depends on Option Base. I prefer VBA.Array. — Tony Dallimore
– Tony Dallimore, Commented Oct 12, 2016 at 13:05
@GSerg. Thanks for the link. I will try this out although it makes little sense to me. Sheets is a Collection. The documentation I have read on collections is that elements can only be accessed by index or key. In general terms what would Collection(X, Y,Z) mean? — Tony Dallimore
– Tony Dallimore, Commented Oct 12, 2016 at 14:21
@TonyDallimore stackoverflow.com/documentation/vba/5838/collections — Slai
– Slai, Commented Oct 12, 2016 at 14:23

score 1 · Accepted Answer · 2016-10-12 13:45:44Z

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)

edited Oct 12, 2016 at 13:45

answered Oct 12, 2016 at 13:16

user6432984

Sign up to request clarification or add additional context in comments.

2 Comments

GSerg Over a year ago

The only problem though is that it is common for Excel sheet names to contain commas ("Profits, monthly")...

user6432984 Over a year ago

@GSerg Updated. Thanks again

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related