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](http://stackoverflow.com/q/8850984/11683) without calling `Array`. — GSerg, Oct 12 '16 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, Oct 12 '16 at 13:05
@TonyDallimore In `Sheets(X)` the `X` [can be an array](https://msdn.microsoft.com/en-us/library/office/ff193217.aspx#Anchor_1). — GSerg, Oct 12 '16 at 13:23
@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, Oct 12 '16 at 14:21
@TonyDallimore http://stackoverflow.com/documentation/vba/5838/collections — Slai, Oct 12 '16 at 14:23
@TonyDallimore `Sheets` is not *the* `Collection`, it's *a* collection. This particular collection specifically accepts an array as the index and returns union of the sheets listed in the array. There is still one argument to the indexer method, it's just that singe argument is an array. — GSerg, Oct 12 '16 at 14:24
@Slai Thanks for the link but I can find nothing to contradict my statement that a collection item is identified by its index or its key. — Tony Dallimore, Oct 12 '16 at 16:38
@GSerg Where does "`Sheets` is not _the_ `Collection`, it's a collection" come from? Excel VBA has many collections of which `Sheets` is one. I frequently create my own collections. If I can move multiple elements within any collection in this way then this is a feature of which I was not previously aware. If it is a feature unique to `Sheets` or `Worksheets` then I am unhappy because I always found inconsistencies in syntax leads to errors. — Tony Dallimore, Oct 12 '16 at 16:48
@TonyDallimore It comes from me. `Sheets` is a collection as opposed to [the `Collection`](https://msdn.microsoft.com/en-us/library/office/gg251465.aspx). There is no standard regarding what other "a-collection"s support because they are not related to each other and it is logical that they accept what makes sense in their area of use. It makes sense for `Sheets` to accept an array index because it has useful applications. It is not related to moving elements or interacting with other a-collections. — GSerg, Oct 12 '16 at 16:56
@GSerg I have been experimenting and you are correct. Although Sheets is always described as a collection it is not a member of the class Collection. A sub class can have properties and methods that are not inherited from the parent so I do not understand why Sheets is not a member of class Collection. A poor class structure can lead to difficult to locate errors which perhaps explains a lot about Excel. In `Sheets(X).Move`, X can be an index (number), a key (string) or a Param Array holding either numbers or strings or a mixture. — Tony Dallimore, Oct 12 '16 at 20:19
@GSerg The sheets to be moved are sorted into alphabetic sequence before moving. I have learnt something today so thank you. — Tony Dallimore, Oct 12 '16 at 20:20

score 1 · Answer 1 · 2016-10-12T13:45:44.503

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 '16 at 13:45

answered Oct 12 '16 at 13:16

1

The only problem though is that it is common for Excel sheet names to contain commas (`"Profits, monthly"`)... – GSerg Oct 12 '16 at 13:35
@GSerg Updated. Thanks again – Oct 12 '16 at 13:46

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

1 Answers1