Excel VBA Copying Row n Times

In Excel Occasionally you will have base data which needs to be repeated n times. This can be time consuming and this article aims to simplify that process. Repeating rows in Excel can be a very time consuming activity especially where there are long lists involved which need to be repeated numerous times. This is usually indicated in the following form.

The data after the procedure should look like the following;

In this simple Excel example the data will be moved from its current location to the side of the sheet. The following is the VBA to achieve this task.

Option Explicit

Sub InsRws() 'Excel VBA to copy n times
Dim rng as Range

For Each rng In Range("C10", Range("C" & Rows.Count).End(xlUp))
Cells(Rows.Count, 11).End(xlUp)(2).Resize(rng.Value, 3)=rng.Offset(, -2).Resize(1, 3).Value
Next rng
End Sub


However, if I want to move the information to a new sheet the following procedure will suffice. It will move the data from the sheet where the macro is run from into the second sheet within the workbook.

Option Explicit

Sub InsRwsRewSH() 'Excel VBA to copy to another sheet.
Dim rng as Range

For Each rng In Range("C10", Range("C" & Rows.Count).End(xlUp))
Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(rng.Value, 3)=rng.Offset(, -2).Resize(1, 3).Value
Next rng
End Sub

The following Excel file shows both VBA procedures.