Link to home
Start Free TrialLog in
Avatar of Dier Eluom
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
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

The code should be doing what you ask. It does by me.
Maybe there are only 3 files in the folder...
Avatar of Dier Eluom
Dier Eluom

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
That line is fine because it get's incremented at the end:
rw = rw + 1
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.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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