Sub GroupData()
Dim sPath as String, sName as String
Dim bk as Workbook, i as Long
Dim sh as Worksheet, lastrow as Long
Dim rng as Range, rng1 as Range, rng2 as Range

sPath = "C:\MyFiles\"
sname = dir(sPath & "*.xls")
do while sName < ""
set bk = Workbooks.Open(sPath & sName)
set sh = bk.worksheets(1)
set lastrow = sh.cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
if instr(1,sh.cells(i,1),"total",vbtextcompare) then
sh.rows(i).Delete
else
if isempty(sh.cells(i,1)) then
set rng = sh.cells(i,1).End(xlup)
sh.cells(i,1).Value = rng
end if
end if
Next
set rng1 = thisworkbooks.worksheets(1) _
.Cells(rows.count,2).end(xlup)(2)
sh.Range("A1").currentRegion.Copy Destination:=rng1
bk.close Savechanges:=False
set rng2 = rng1.Parent.Range(rng1,rng1.End(xldown))
rng2.offset(0,-1).Value = sName
sName = dir()
Loop
rng2.Parent.Range("A1:D1").Value = _
Array("territory","city","item","people")
End sub

Should get you started. I put in the filename for the territory
Assumes you data won't exceed the number of rows in one sheet and that the
code is hosted in the workbook where you want to consolidate the data (on
the first sheet in the tab order)

--
Regards,
Tom Ogilvy


"Marina Limeira" wrote in message
...

I have 120 equal format files about hospital produces in cities.

example: group files.xls are territories files name

City item peoples
A item01 100

B item02 183
item03 432
Total
C item04 34


Are you send oppinion about I imagine create a big database with import
from
120 files .xls
to analysing...

territory | city | item | people
1 A 01 100
1 B 02 183
1 B 03 432
...
3 A 01 343
3 A 02 433

Question, also example from VBA to import from several files.xls to big
centralize file database sheet ?

thanks for helping me

Marina