laukikrikame
New Member
- Joined
- Aug 6, 2018
- Messages
- 1
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
<tbody>
</tbody>
Expected output in master:
Name ID test Daily assignments (Day 1,2,3) (Which i can put manually in the master)
<tbody>
</tbody>
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
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
Name (T10) | |||
Stud ID(T12) | |||
Test score (T7) | |||
Daily assignments. |
<tbody>
</tbody>
Expected output in master:
Name ID test Daily assignments (Day 1,2,3) (Which i can put manually in the master)
John | 115 | 85 | 1 | 4 | 6 | 8 | 7 | 2 |
<tbody>
</tbody>
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