I have about 200 worksheets, all saved as separate files, that need to be
merged into one worksheet. For example, I have AL.xls, FL.xls, and GA.xls.
Each file contains a single worksheet. I need the data in FL.xls and GA.xls
to be in the same worksheet as AL.xls

I found the following code at
http://exceltips.vitalnews.com/Pages...Workbooks.html

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer

On Error GoTo ErrHandler
Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open FileName:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub


It works really well except that it puts the data in the same workbook but
on separate tabs. I need the data all together on one tab. Is there a way to
do this with a macro?

Hi Raewyn

Try this add-in
http://www.rondebruin.nl/merge.htm

Or use the code in the links on the bottom of that page


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Raewyn" wrote in message ...
I have about 200 worksheets, all saved as separate files, that need to be
merged into one worksheet. For example, I have AL.xls, FL.xls, and GA.xls.
Each file contains a single worksheet. I need the data in FL.xls and GA.xls
to be in the same worksheet as AL.xls

I found the following code at
http://exceltips.vitalnews.com/Pages...Workbooks.html

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer

On Error GoTo ErrHandler
Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open FileName:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub


It works really well except that it puts the data in the same workbook but
on separate tabs. I need the data all together on one tab. Is there a way to
do this with a macro?

Thank you. That was exactly what I needed.


"Ron de Bruin" wrote:

Hi Raewyn

Try this add-in
http://www.rondebruin.nl/merge.htm

Or use the code in the links on the bottom of that page


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Raewyn" wrote in message ...
I have about 200 worksheets, all saved as separate files, that need to be
merged into one worksheet. For example, I have AL.xls, FL.xls, and GA.xls.
Each file contains a single worksheet. I need the data in FL.xls and GA.xls
to be in the same worksheet as AL.xls

I found the following code at
http://exceltips.vitalnews.com/Pages...Workbooks.html

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer

On Error GoTo ErrHandler
Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open FileName:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub


It works really well except that it puts the data in the same workbook but
on separate tabs. I need the data all together on one tab. Is there a way to
do this with a macro?