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?

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?

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?

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?

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?

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

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?

Many Many Thanks

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

Ant

"Joel" wrote:

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

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?

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

--

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


"drumbumuk" wrote in message ...
Many Many Thanks

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

Ant

"Joel" wrote:

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

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?