Copy specific cells from all workbooks in folder to single master file.

[laukikrikame]

Hi Folks,

I need some help with copying data from a specific sheet(Aug) of multiple workbooks in a folder to a specific sheet (Sheet1) of the parent workbook. I have tried a code which i learnt from internet but it is returning only the name of file and value of a single cell.

Below are the details:

*60 files are saved in one folder (1 for each user).
*Users track their daily productivity and various results are saved in different cells on the same worksheet.

*I have created one master file in another folder.
*The master file should fetch the data in cells R3- R33, T7, T10-13 of sheet named 'Aug' from each file in the folder and display in a row
EG.
Child file:
T10 Name
T12 Stud ID[TABLE="width: 300"]
<tbody>[TR]
[TD]Name (T10)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stud ID(T12)[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Test score (T7)[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Daily assignments.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Expected output in master:
Name ID test Daily assignments (Day 1,2,3) (Which i can put manually in the master)[TABLE="width: 500"]
<tbody>[TR]
[TD]John [/TD]
[TD]115[/TD]
[TD]85[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I am using the below code currently which returns the file name (which i have saved as per stud name) and value in cell T7 (R7C20). I got this code from internet and not familiar with the FormulaR1C1 function.

Kindly help, Thanks in advance.

Sub Test()
' Adjust the path below as required
MyPath = "Z:\IMP\APT " ' Set the path.
myname = Dir(MyPath, vbNormal) ' Retrieve the first entry.
Do While myname <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If myname <> "." And myname <> ".." Then
If (GetAttr(MyPath & myname) And vbNormal) = vbNormal Then
ActiveCell.FormulaR1C1 = "='" & MyPath & "[" & myname & "]Aug'!R7C20"

' change the part after the ] to your sheets name
' also change the R1C1 on the end to pick up the cell you want ie R2C3 for cell C2
' do NOT change the 1st one (.FormulaR1C1) this is part of the command.
ActiveCell.Offset(0, 1).Value = myname
ActiveCell.Offset(1, 0).Select
End If
End If
myname = Dir
Loop
End Sub