Copy row and it's contents based on value in X cell.

Closed
vmvmvmdew Posts 1 Registration date Wednesday January 25, 2017 Status Member Last seen January 25, 2017 - Updated by Ambucias on 25/01/17 at 05:09 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 26, 2017 at 11:40 AM
Hi everyone, this is my first post so I hope I'll explain myself clearly!

I have a spreadsheet with the following content:



(Column I (in yellow) contains a vlookup formula which is taking data from the small table in range A1:B8).

What I'm trying to do is copy the whole row of data based on the number in column I. So for instance, if cell I17 has number 6 in. I would then like to see that whole row repeated six times below. Here is an example I've done with a copy/paste:



I could just do it manually but the real spreadsheet has thousands of lines. I've seen examples of macros that can do similar things but I can't get it to match for this actual requirement.

Could anyone help?

Many thanks!

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 26, 2017 at 11:40 AM
Hi vmvmvmdew,

Welcome to the forum!

You explained well, although your mini table doesn't make much sense if it just subtracts one. Why not use formula in I11: =H11-1?

Give the code below a try and see if it yields the desired result:
Sub RunMe()
Dim x, y, z As Integer

y = 11

Do Until Range("I" & y) = vbNullString
    x = Range("I" & y).Value
    z = x
    Do Until x = 0
        Rows(y).Copy
        Rows(y).Insert shift:=xlUp
        x = x - 1
    Loop
    y = y + z + 1
Loop

Application.CutCopyMode = False

End Sub


Best regards,
Trowa