Your efforts are very nice. Please try to define the variabels names more
clearly and try to put more comments for easy understanding of beginners.
But code is very useful.

"Tom Ogilvy" wrote:

Assume all the files (and only the files) are in a subdirectory - C:\MyFolder
for illustration

Sub GatherInformation()
Dim sPath as String, sName as String
Dim bk as Workbook, rng as Range
Dim rng1 as Range
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName < ""
set bk = workbooks.Open(sPath & sName)
set rng = Thisworkbooks.worksheets(1).Cells(rows.count,1).En d(xlup)(2)
with bk.Worksheets(1)
set rng1 = .Range(.Cells(1,1),.cells(rows.count,1).End(xlup))
end with
rng1.entirerow.copy destination:=rng
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy]

"Gunnar" wrote:

I have by bad system design in earlier stage received about 500 pcs of small
individual Excel files of exakt the same design but with data of different
items. Each file normally holds between 20 - 30 records (rows).
I now have a need to aggregate all these files into one single Excel or
Access file and would like to avoid all the work with to open each invidual
file and copy and paste.
Does any kind person know any practical method available?