sheets(array).copy Error - Excel 2010

[mcwillis81]

Hi All,

HELP, I'm confused....

The below code works at home on excel 2016 however in work where we use 2010 it fails on the wb.sheets(arr).copy line any suggestions? is it really a version difference or am i missing something?

Thanks in advance for any suggestions

Sub copysheets()


Dim Tbl As ListObject
Dim arr() As String
Dim WB As Workbook, newWB As Workbook
 
Set WB = ThisWorkbook
Set Tbl = Sheet5.ListObjects(1)


ReDim arr(1 To Tbl.ListRows.Count)


    For i = 1 To Tbl.ListRows.Count
    
        arr(i) = Tbl.DataBodyRange(i, 1)
    
    Next i


Set newWB = Workbooks.Add


ThisWorkbook.Sheets(arr).Copy newWB.Sheets(1)


End Sub

 

[Norie]

Have you checked that all the values in the array are valid sheet names, with no spelling errors/errant spaces etc., that exist in the workbook the code is in?

PS What's the error message?

 

[JackDanIce]

Tbl is declared as a list object and set to Sheet5

Set Tbl = Sheet5.ListObjects(1)
ReDim arr(1 To Tbl.ListRows.Count)
For i = 1 To Tbl.ListRows.Count
     arr(i) = Tbl.DataBodyRange(i, 1)    
Next i

This seems to be creating an array of Tbl objects from Sheet5, which I'm guessing contain sheet names you're trying to copy to a new workbook?

It may be the the values themselves are not valid sheet names, but doesn't explain why works in xl2016 and not xl2010

Can you give examples of what the array contains?

 

[mcwillis81]

Hi Norie,

I copied and pasted them directly from the tabs when placing them into the table so i'm confident they are correct.

I intended to come home and duplicate the error and debug but it worked!!!!! i was hoping someone may have come across the issue before as i did spend a bit of time debugging with no joy in work.

I will update the thread with the error msg tomorrow but i believe it was subscript out of range?

thanks,

 

[mcwillis81]

Tbl is declared as a list object and set to Sheet5

Set Tbl = Sheet5.ListObjects(1)
ReDim arr(1 To Tbl.ListRows.Count)
For i = 1 To Tbl.ListRows.Count
     arr(i) = Tbl.DataBodyRange(i, 1)    
Next i

This seems to be creating an array of Tbl objects from Sheet5, which I'm guessing contain sheet names you're trying to copy to a new workbook?

It may be the the values themselves are not valid sheet names, but doesn't explain why works in xl2016 and not xl2010

Can you give examples of what the array contains?

Hi Jack

That's right, the array holds a string of sheet names which i'm then copying to a new workbook.

The sheet names should be valid as i copied and pasted them into the table from the tabs.

Its a single column table with simple names such as "Front Page" etc

Its a strange one!?

 

[JackDanIce]

This will suggest if there is an issue with the values in the data table as sheet names:

Sub test()


    Dim w   As Long
    Dim arr As Variant
    Dim msg As String
    Dim tbl As Object
    Set tbl = Sheet5.ListObjects(1)


    ReDim arr(1 To Worksheets.Count)
    
    For w = 1 To tbl.Rows.Count
        With sheets(tbl.Cells(w, 1).Value)
            If Evaluate(.Name & "!A1") Then
                arr(w) = sheets(tbl.Cells(w, 1).Value).Name
            Else
                msg = msg & tbl.Cells(w, 1).Value & ", "
            End If
        End With
    Next w
    
    If Len(msg) Then
        msg = Left$(msg, Len(msg) - 2)
        msg = Replace("The following sheet names are not found: @L@L@N)", "@N", msg)
        msg = Replace(msg, "@L", vbCrLf)
        MsgBox msg, vbExclamation, "Sheets not found"
    Else
        sheets(arr).Copy
    End If


    Erase arr: Set tbl = Nothing


End Sub

Otherwise copy the sheets into a new workbook.

In your code, where is newWB declared or created? (Which code above doesn't seem to need).
Is it because you're trying to copy to newWB.Sheets(1) but there is a mis-match in count of sheets copied, to a single sheet in newWB?

 

[RoryA]

If it’s a subscript out of range error, at least one of the sheet names is wrong. Have you checked there are no blank rows in the table for example?

 

[mcwillis81]

Hi All,

Thanks for the suggestions so far I'll try them tomorrow and post an update.