Merge all files in directory

[Guest]

I have 354 Excel files that are setup the same(col & rows). How do I loop
thru all files, extract data and copy to a Master worksheet? Number of rows
is not an issue.

 

[Tom Ogilvy]

How are you going to identify the 354 files. Are you going to type there
names in? Are they all in one directory.

see Ron de Bruin's site for some general code:

http://www.rondebruin.nl/index.html

 

[Walt Weber]

I'll assume you can place all these files in the same
directory and a master file there too to receive all the
data. The following code placed in a module of the
master file with your addition of Copy and Copy To range
definitions should help get you there. Check the help
system for the Dir function to see how this will step
through all .xls files in the directory (There's a sample
there somewhat similar to the following).

Your "files that are setup the same(col & rows)" leads me
to think you can take it from here.

Sub MergeFiles()
Dim CurFile As String, CopyRng As Range, _
CopyToRng As Range
ChDir ThisWorkbook.Path
CurFile = Dir("*.xls")
Do While CurFile <> "" and CurFile <>
ThisWorkbook.Name
Workbooks.Open CurFile

Set CopyRng = '<<<<You Define the range to be
'copied from each workbook here. It
'could be complicated to do this if the
'range is not identical in each of the
'source workbooks.
Set CopyToRng = '<<<<You define the range to
'receive the copied data from CurFile
'here. You will have to work out some
'logic to shift this range definition
'between source files so as not to
'overwrite prior data."
CopyRng.Copy Destination:=CopyToRng

Workbooks(CurFile).Close savechanges:=False
CurFile = Dir ' Get next entry.
Loop
Set CopyRng = Nothing: Set CopyToRng = Nothing
End Sub

Best Regards,
Walt Weber