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

Question

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!

TrowaD · Answer

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

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

1 response

Similar discussions

Subscribe To Our Newsletter!