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

Posts
1
Registration date
Thursday February 16, 2017
Last seen
February 16, 2017
- - Latest reply: TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
- 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 

1 reply

Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
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
Posts
1
Registration date
Thursday February 16, 2017
Last seen
February 21, 2017
-
Hi Trowa

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

Rgds
David
TrowaD
Posts
2440
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 13, 2018
-
Awesome