Gather info from mulitple files and aggregate into one

[jtriggs1941]

I have a form that reps fill out and place in a server folder. I have the form set up so that their responses link from the form cells into cells M2:BB2. Is there any way I can automatically get the data from each of these files to feed into one file? This way I can aggregate it into reports for the boss. Please advise.

Thanks,
Terri

 

[ravishankar]

A macro may help you
List all excel files to be collated in column a (terry.xls)

paste the following code in the window got from Alt F8
keep all excel files open.

Sub from()
For a = 2 To 3 ( or as many excel files to be added)
b = Cells(a, 1)
For c = 13 To 42
Cells(a, c + 1) = Workbooks(b).Worksheets("sheet1").Cells(1, c)
Next c
Next a
End Sub

run the macro
good luck

Ravi