Dier Eluom
asked on
Modify the VB code
What I want to be able to do is iterate through a spreadsheet. At this point the code just puts all the results into just the same three 3 rows instead of from row 10 to row 32.
It has something to do with the line in the code where it says rw = 10. How do you turn this into a range from rw 10 to rw 32?
NCR_Master.xlsm
It has something to do with the line in the code where it says rw = 10. How do you turn this into a range from rw 10 to rw 32?
NCR_Master.xlsm
ASKER
If you look at the code I think this is the line that needs changing:
Public Sub ListWorkbooks()
Dim Directory As String
Dim FileName As String
Dim IndexSheet As Worksheet
Dim rw As Long
Directory = "" & Range("Path").Value & ""
If Right(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If
rw = 10 [this line needs changing because I want it to pull data from rw 10 to rw 32 so row 10 B,C,E, row 11, b,c,e and so on]
Set IndexSheet = ThisWorkbook.ActiveSheet
FileName = Dir(Directory & "*")
Do While FileName <> ""
IndexSheet.Cells(rw, 2).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!A7"
IndexSheet.Cells(rw, 2).Copy
IndexSheet.Cells(rw, 2).PasteSpecial Paste:=xlPasteValues
Public Sub ListWorkbooks()
Dim Directory As String
Dim FileName As String
Dim IndexSheet As Worksheet
Dim rw As Long
Directory = "" & Range("Path").Value & ""
If Right(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If
rw = 10 [this line needs changing because I want it to pull data from rw 10 to rw 32 so row 10 B,C,E, row 11, b,c,e and so on]
Set IndexSheet = ThisWorkbook.ActiveSheet
FileName = Dir(Directory & "*")
Do While FileName <> ""
IndexSheet.Cells(rw, 2).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!A7"
IndexSheet.Cells(rw, 2).Copy
IndexSheet.Cells(rw, 2).PasteSpecial Paste:=xlPasteValues
That line is fine because it get's incremented at the end:
rw = rw + 1
rw = rw + 1
ASKER
I want it to search row 10 and enter the data into B10, C10 and E10
then row 11 B10, C10, E10 then row 12 and B10, C10 and E10 and so on to row 32.
then row 11 B10, C10, E10 then row 12 and B10, C10 and E10 and so on to row 32.
At this point the code just puts all the results into just the same three 3 rows instead of from row 10 to row 32
want it to search row 10 and enter the data into B10, C10 and E10
then row 11 B10, C10, E10 then row 12 and B10, C10 and E10 and so on to row 32.
I'm confused.
ASKER
I have a spreadsheet that this one pulls data from. However, it pulls the data from the spreadsheet and only uses row 10 so you see it iterating through the names and scores but only in row 10 cells B, C and D. I end up with one name - the last one. I want it to copy the data from the cells like; A11- Bob Smith (B11) 12 (C11) 22 (D11)
A12 Dave Jones (B12) 10(C12) 18 (D12)
and so on down to row 32.
A12 Dave Jones (B12) 10(C12) 18 (D12)
and so on down to row 32.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: MacroShadow (https:#a42489416)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: MacroShadow (https:#a42489416)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
Maybe there are only 3 files in the folder...