Looping through rows and copy cell values to another worksheet

Question

Intention: I have a list of data in sheets(inputs).column BN (the number of rows that has value will vary and hence I created a loop that will run the macro until the active cell is blank).

My macro starts from Range (BN2) and stretches all the way down column BN, it stops only when it hits a blank row. Desired result for the macro will be to start copying the cell value in sheet(input).Range(BN2) paste it to sheet(Output).Range(A2:A10).

For example, if "Peter" was the value in cell sheet(input),range(BN2) then when the marco runs and paste the value into sheet(Output) range(A2:A10). i.e. range A2:A10 will reflect "Peter". Then the macros loop back to sheet(inputs) and copies the next cell value and paste it to range(A11:A19) Example: "Dave" was the value in sheet(inputs) Range(BN3), then "Dave" will be paste into the next 9 rows in sheet(mywork).Range(A11:A19). A11:A19 will reflect "Dave" Again repeating the same process goes back to sheet(input) this time range(BN4), copies the value goes to sheet(Output) and paste it into A20:A29. Basically the process repeats....

The macro ends when the active cell in sheet (input) column A is empty.

Any update on this request, can you someone please help me on this on priority note. — Hari Prasad
Exactly which part are you having a problem with? If you don't know any VBA then this is probably not the place: if you know some VBA then it's best to post any code you have, even if it's not working. — Tim Williams

user1016274 user1016274 · Accepted Answer · 2015-09-27T11:42:34

This does the job:

Option Explicit

Sub MakeNameColumn()
' http://stackoverflow.com/questions/32804087/looping-through-rows-and-copy-cell-values-to-another-worksheet
' 2015-09-27 E/S/P

    Dim shIn As Worksheet, shOut As Worksheet
    Dim data As String
    Dim j As Long, inCol As Long, inPos As Long, outPos As Long

    Set shIn = Sheets("input")
    Set shOut = Sheets("output")
    outPos = 2
    inPos = 2
    inCol = Columns("BN").Column
    Do
        data = shIn.Cells(inPos, inCol).Text
        If data = "" Then Exit Do
        ' copy to output sheet
        For j = 0 To 8
            shOut.Cells(outPos + j, 1).Value = data
        Next j
        outPos = outPos + 9
        inPos = inPos + 1
    Loop
    shOut.Activate
End Sub

As simple as the code is it will show you how to use the elements that make up a VBA macro: declarations, addressing cells, getting cell contents, looping, avoiding the ".select" statement whenever you can, and some more.

But clearly, this is not a "code take-away" service. I see that you're in a pinch. Once you get more proficient please get others started with VBA on StackOverflow.

Looping through rows and copy cell values to another worksheet

1 Answers