Use a macro - run the code below, and select the files of interest in thedialog.
HTH,
Bernie
MS Excel MVP
Sub MergeUserSelectedFiles()
Dim FileArray As Variant
Dim myB As Workbook
Dim i As Integer
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 before:=ThisWorkbook.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
Use a macro - run the code below, and select the files of interest in the
dialog.
HTH,
Bernie
MS Excel MVP
Sub MergeUserSelectedFiles()
Dim FileArray As Variant
Dim myB As Workbook
Dim i As Integer
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 before:=ThisWorkbook.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
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 before:=myNB.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
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 before:=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 needto 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?)
and ideally, a fix?
Am using Excel03, but could run in 07 if that'd fix it.
cheers
sounds like you need a database rather than spreadsheets
That's the correct sound... but it's required in Excel.
cheers
After serious thinking David T wrote :
Have you tried reading the files via ADODB and writing the data to a
newly inserted sheet for each file? This would obviate having to open
the workbooks so the only tax on resources will be the newly added
sheets. (This assumes the same variable to store the recordset is
reused for each file)
I'll get back to you on that after discovering what ADODB is.
cheers
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.