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

Report
Posts
1
Registration date
Wednesday January 25, 2017
Status
Member
Last seen
January 25, 2017
-
Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
-
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 reply

Posts
2676
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2020
448
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!