Copy row by x times where value x is cell value of the range

Solved/Closed
davidang Posts 1 Registration date Thursday February 16, 2017 Status Member Last seen February 16, 2017 - Feb 16, 2017 at 07:49 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Feb 21, 2017 at 11:59 AM
I need to scan each value in a column A and use the value in that column to copy the current row x times to another sheet. The second sheet is to be used as a source document for labels mail merge

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Feb 21, 2017 at 10:55 AM
Hi Davidang,

The following code will work when you call your source sheet "Sheet1" and your destination sheet "Sheet2" or find and change those references in the code.

Here is the code:
Sub RunMe()
Dim x As Integer

Sheets("Sheet1").Select

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    x = cell.Value
    cell.EntireRow.Copy
    Sheets("Sheet2").Select
    Do Until x = 0
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
        x = x - 1
    Loop
Next cell

Application.CutCopyMode = False

End Sub


Best regards,
Trowa
0
daveang70 Posts 1 Registration date Thursday February 16, 2017 Status Member Last seen February 21, 2017
Updated by daveang70 on 21/02/17 at 11:39 AM
Hi Trowa

I tested it. Yes it works and fulfill my requirement. Thanks a million.

Rgds
David
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Feb 21, 2017 at 11:59 AM
Awesome
0