Hi!
The task now is to Open multiple workbook then combine into single
workbook but different sheets.
Pls help how to do in vba?

Thanks

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 befo=ThisWorkbook.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub



"geniusideas" wrote in message
...
Hi!
The task now is to Open multiple workbook then combine into single
workbook but different sheets.
Pls help how to do in vba?

Thanks

On Nov 24, 10:34*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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 befo=ThisWorkbook.Worksheets(1)
* * * myB.Close False
* *Next i
Else:
* *MsgBox "You clicked cancel"
End If
End Sub

"geniusideas" wrote in message

...

Hi!
The task now is to Open multiple workbook then combine into single
workbook but different sheets.
Pls help how to do in vba?

Thanks

Thanks bernie..

It's work. if I want to combined into new workbook how to do because
currently if I run it will combined into current workbook. Pls
help .Thank

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



"geniusideas" wrote in message
...
On Nov 24, 10:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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 befo=ThisWorkbook.Worksheets(1)
myB.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub

"geniusideas" wrote in message

...

Hi!
The task now is to Open multiple workbook then combine into single
workbook but different sheets.
Pls help how to do in vba?

Thanks

Thanks bernie..

It's work. if I want to combined into new workbook how to do because
currently if I run it will combined into current workbook. Pls
help .Thank

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?) and ideally, a fix?

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



cheers
David T

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