Open multiple excel file, get data into a single file

[hunza]

I have 144 excel files in one folder, their name ranged from 001, 002, 003, .....156 (File names correspond to subject ID, some of the subjects are missing)

I want to get partial data from each file and then paste into a new file to generate a table with three col. like below

FileName (Name of file) SubjectName (Subject name preset in sheet:subject info) TST (Sum of N col of every excel File:sheet:data)


I have already develop a macro which can open & read certain info from single individual excel file and then paste in the single excel file. I need to put in the loop, so it can work with all 144 files as once.

Sub CopyCells()
Workbooks.Open Filename:="D:\Hunza\Data\Munir\Researches\Armband 2010\data\001.xls" 'increment required in file name
ThisWorkbook.Activate
Range("D2").Select 'increment required in D2
ActiveCell.FormulaR1C1 = "=SUM([001.xls]Data!C14)"
Windows("001.xls").Activate 'increment required in file name
Sheets("Subject Info").Select
Range("B1").Copy
ThisWorkbook.Activate
Sheets("sheet1").Activate
'*** Select the destination cell
Range("C2").Select 'increment required in C2
ActiveSheet.Paste
Workbooks("001.xls").Close 'increment required in file name
End Sub

Thanks
Munir

 

[G2K]

check out the below link.

http://www.rondebruin.nl/copy3.htm

Hope it helps

 

[hunza]

Thanks, i will check....it seems a BIG Macro