Excel VBA For Loop to Duplicate Data

Question

I'm trying to create a for loop that looks the first row in my data source (row 3) and then it pastes that in a new reference sheet, but then I want the loop to paste the same data again just below it. So row's 3 and 4 in the reference sheet will be identical. Then I want it to look at row 4 in my data source and do the same thing, i.e. paste the data into row's 5 and 6 in the reference sheet.

This is my code so far - right now it only pastes row 3 once because my range is fixed. How do I fix this to make it paste one more time and then look at the next row in the data source?

RowCount = Dump.Cells(Rows.count, 1).End(xlUp).row
RefRow = ref.Cells(Rows.count, 1).End(xlUp).row

With ThisWorkbook
    With Dump
        For i = 1 To RowCount
            .Range("A3:AO3").Copy Destination:=ref.Range("A3")
            .Range("A3:AO3").Copy Destination:=ref.Range("A" & RefRow)
            RefRow = RefRow + 1
            row = row + 1
        Next i

    End With
End With

Have a look at OffSet. You can then get row count in your FOR loop and use it to paste the values in last row and the one after that — Zac
– Zac, Commented Dec 13, 2018 at 16:46

Fabricio Montagnani · Accepted Answer · 2018-12-13 18:32:56Z

2

Try this:

Sub CopyPasteDuplicateRows()
Dim i, z As Integer
Dim rg, rg2 As Range
Dim ws, ws2 As Worksheet

Set ws = Sheets("NameOfTheSourceSheet")
Set ws2 = Sheets("NameOfTheDestinySheet")

For i = 3 To ws.Range("A" & Rows.Count).End(xlUp).Row
    Set rg = ws.Range("A" & i & ":AO" & i)
    If i = 3 Then
        Set rg2 = ws2.Range("A3:A4")
    Else
        z = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        Set rg2 = ws2.Range("A" & z & ":A" & z + 1)
    End If

    rg.Copy
    rg2.PasteSpecial xlPasteAll
Next

End Sub

answered Dec 13, 2018 at 18:32

Fabricio Montagnani

461 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA For Loop to Duplicate Data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related