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" wrote:

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" wrote:

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" wrote:

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" wrote:

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?