Copy row by x times where value x is cell value of the range [Solved/Closed]

davidang 1 Posts Thursday February 16, 2017Registration date February 16, 2017 Last seen - Feb 16, 2017 at 07:49 PM - Latest reply: TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen
- 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
See more 

3 replies

TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen - Feb 21, 2017 at 10:55 AM
0
Thank you
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
daveang70 1 Posts Thursday February 16, 2017Registration date February 21, 2017 Last seen - Feb 21, 2017 at 11:39 AM
Hi Trowa

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

Rgds
David
TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen - Feb 21, 2017 at 11:59 AM
Awesome