Excel VBA: Loop through cells and copy values to another worksheet

Question

Facing some diffculty in achieving the desired result. Current results = for example the last value in sheets(input) is "Peter" then the entire column in sheets(sheet1) will just reflect Peter

Desired result = last 2 rows value in sheets(input) is "david" then "peter" what i hope to achieved in sheets(Sheet1) is range(B5:B8) reflecting "david" and Range(B9:B12) "Peter"

Hope you guys here can give some advice on this matter. thanks in advance :)))

Sub playmacro()
    Dim xxx As Long, yyy As Long
    ThisWorkbook.Sheets("Input").Range("A2").Activate
    Do While ActiveCell.Value <> ""
        DoEvents
        ActiveCell.Copy
        For xxx = 2 To 350 Step 4
            yyy = xxx + 3
            Worksheets("Sheet1").Activate '"select Sheet1"
            With ActiveSheet
                'copy the first active cell value from worksheet "input" and paste onto "sheet1" range("B2:B5") basically 1 cell value will occupy 4 rows in "sheet1"
                'then jump to the next empty row, return back to worksheet "input" copy the next row value and paste the data into range("B6:B9")
                'the loop will keep repeating until sheet "input" activecell value is blank
                .Range(Cells(xxx, 2), Cells(yyy, 2)).PasteSpecial xlPasteValues
            End With
        Next xxx
        ThisWorkbook.Sheets("Input").Select
        ActiveCell.Offset(1, 0).Activate
    Loop
    Application.ScreenUpdating = True
End Sub

Peter Albert Peter Albert · Accepted Answer · 2013-06-04T12:44:20

Not sure I fully understood your requirements, but maybe this is what you need:

Sub PlayMacro()
    Dim lngLastRow as Long
    With Sheets("Input")
        lngLastRow = .Range(.Rows.Count, 1).End(xlUp).Row
        Sheets("Sheet1").Range("B5:B8").Value = .Range(lngLastRow-1,1).Value
        Sheets("Sheet1").Range("B9:B12").Value = .Range(lngLastRow,1).Value
    End With
End Sub

Excel VBA: Loop through cells and copy values to another worksheet

1 Answers