String Array of Worksheet names succeeds in Excel 2003, but fails in excel 2010 (Why?)

[tiggerdad]

I have workbook which creates an array of worksheet names, then copies those sheets to a new book. It worked fine in excel 2003, but error in excel 2010:
error occurs right after .copy

 Runtime error:
Method 'Copy' of object 'Sheets' failed

Copy seems to be created (there's a book1, for example), but excel locks and i'm unable to view it and have to force application closed.
I realize an alternative would be to copy one sheet at a time into a new book, but this seems like a clean (alternative) approach that fails in excel 2010. is there something that's changed (or is there some coding change required to make in work in 2010)?

Thanks,

Sub Break_Out_Regions()
Dim OrigBk As Workbook, Newbk As Workbook
Dim Ws As Worksheet
Dim sReg As String, basepath As String, mgmtpath As String, sTab As String
Dim NewFilename As String, sPath As String, Arr() As String
Dim iYr As Integer, i As Integer, j As Integer, N As Integer
N = 0
Set OrigBk = ThisWorkbook

For Each Ws In OrigBk.Worksheets
    If Ws.Visible = xlSheetVisible And Ws.Range("a1").Value = "REPORT" Then 'criteria to select sheets
        N = N + 1
        ReDim Preserve Arr(1 To N)
        Arr(N) = Ws.Name
    End If
Next Ws
With OrigBk
    .Worksheets(Arr).Copy
End With
Set Newbk = ActiveWorkbook
.....

 

[byundt]

I get a Type Mismatch error message in Excel 2010 when I run your macro and there aren't any worksheet names in the array. The fix is to test the value of N:

If N > 0 Then .Worksheets(Arr).Copy

I did not get any error message if there were one or more worksheet names in the array already.

 

[strongm]

How many sheets are you copying? Different version of Excel seem to have different limits on this.

 

[tiggerdad]

byundt & strongm,
thanks for the responses.


I added the following:

For j = 1 To N
    Debug.Print Arr(j)
Next j
Debug.Print UBound(Arr)

after building the array and got a response:

MasterReport
Region 1
Region 2
Region 3
Region 4
 5

so the array is not empty, nor are there more sheets than excel can handle. Again, this same code worked in Excel 2003.

I also looked at [link ]

http://msdn.microsoft.com/en-us/library/ee836187.aspx

[/url] but couldn't see change to array functionality.

thanks,
Alan

 

[byundt]

Alan,
Are you able to post a link to a workbook that demonstrates your problem? I tested my code with Excel 2010 prior to posting, so I suspect there something about your workbook is causing difficulty.

Brad

 

[tiggerdad]

You're right. When I copied the sheets manually, it caused Excel to Crash. I had inherited this report from someone else (who had been compiling manually). there must have been some goofy characters in there.

Thanks.