copying many separate xls files into one comprehensive one

[sean]

Gang,

I have 20 xls files each with 20 worksheets. Now, I need to copy all of
their contents into an xls file that will contain 400 worksheets. Pity,
there's no merge for this sort of stuff (only different versions of the
same xls file). any help apreciated.

-S

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Frank Kabel]

Hi
see:
http://www.rondebruin.nl/copy3.htm

 

[Ron de Bruin]

Try this for all the files in the folder C:\Data

Sub TestFile()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets.Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub