On Friday, June 29, 2012 10:58:12 AM UTC+10, David T wrote:
On Thursday, November 26, 2009 9:47:14 AM UTC+10, Bernie Deitrick wrote:
See code below.

HTH,
Bernie
MS Excel MVP


Sub MergeUserSelectedFiles()
Dim FileArray As Variant
Dim myB As Workbook
Dim myNB As Workbook
Dim i As Integer

Set myNB = Workbooks.Add

FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
For i = LBound(FileArray) To UBound(FileArray)
Set myB = Workbooks.Open(FileArray(i))
myB.Worksheets.Copy befo=myNB.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub



News from June 2012:

Bernie's code looked like it would do what I needed, and it does, but only up to a specific point.
I have a folder containing 119 single sheet workbooks, all have the identical format, and they all have a sheet name of "pn_[unique number]". I need to combine all of those worksheets into a single workbook and maintain their tab names. Bernie's code above did that, but after it adds the 62nd worksheet to the new mega-workbook it falls over with a window that says; "Excel cannot complete this task with available resources. Choose less data or close other applications." Closing this box then allows a VB window to pop-up that says; Run-time error '1004': Method of 'Copy' of object 'Sheets' failed.

Can anyone advise me of what these mean? (Is there a maximum number of wsheets allowed?)

Further reading reveals that it's a memory problem. (They're big sheets) Bugger.

and ideally, a fix?

Am using Excel03, but could run in 07 if that'd fix it.



cheers

David T