Excel - merging numerous files

[drumbumuk]

I have about 40 xl files, that I want to merge - quickly! But i dont want to
import row one as this just contains field names.

Any suggestions?

 

[Joel]

the best way is to put all the files in one directory and then run the macro
below. Modify the Folder location and the Sheet Names ("Sheet1").


Sub MergeBook()

Folder = "c:\temp\"

Set DestSheet = ActiveSheet
FName = Dir(Folder & "*.xls")
Do While FName <> ""
LastRow = DestSheet.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Set OldBook = Workbooks.Open(Filename:=Folder & FName)
With OldBook.Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Copy Destination:=DestSheet.Rows(NewRow)
End With
OldBook.Close savechanges:=False
FName = Dir()
Loop
End Sub

 

[drumbumuk]

Many thanks for that, but i dont know how to use macros - i assumed to use MS
Visual Basic editor, copy the script below and change 'Folder' and 'Fname'?

Do i need to use the full folder/fname address?

 

[Joel]

You should in VBA menu Insert - Module. And put code in new module. The
Folder need to be the complete folder name with a backslash at the end like I
did.

 

[drumbumuk]

Joel

Many thanks - learn something new every day. Now have discovered that the
server that sends these files has given each sheet a unique name, so i guess
i will be renamining them.

Thanks

 

[Joel]

How many sheets are in the workbooks. Yo can always use a index number to
get the sheets. Instead of sheets("Sheet1") use Sheets(1). This will get
the first sheet no matter what the name is. If there was more than one
worksheet in each book you can modify the macro to get all the data.

 

[drumbumuk]

Many Many Thanks

This has worked brilliantly. Save me a lot of time!

Ant

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/merge.htm