Copy One Row of Data and Paste Every Nth Row in a Range

[yomerves]

I'm having trouble doing a task that should be relatively straightforward but I can't seem to figure it out:

I have a row of data in a spreadsheet, and I want to take that row and copy it, and then insert it 20 rows down (insert that row and shift the existing data down a row). I then want to paste that row of data every 20th row for the next 1000 rows in the spreadsheet. Is there a Macro I can write that does this? I have a Macro to copy and insert the row once, but I haven't been able to loop it so that the row is pasted multiple places at once.

Thanks for your help!

 

[kevin9999]

Not 100% certain of your intention (insert every time or overwrite) but give the following a try on a copy of your workbook, change the sheet names and row-to-copy as appropriate.

Option Explicit
Sub Copy_Row2_Every_20_Rows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")       '<~~ *** Change sheet name to suit ***
    
    Dim i As Long
    For i = 1000 To 20 Step -20
        ws.Rows(2).Copy                 '<~~ *** Change this to the row you want to copy ***
        Rows(i).Insert
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[yomerves]

Thanks for this! I think this might work for me if I tweak it. My intention is to insert the row each time and not overwrite. Is the code starting at the bottom and working up? Does it make a difference if I go down from the top or up from the bottom?

 

[Kevin_Li]

Operations involving inserting or deleting rows will change the objects to be operated on later, so they are generally done from bottom to top

 

[kevin9999]

Thanks for this! I think this might work for me if I tweak it. My intention is to insert the row each time and not overwrite. Is the code starting at the bottom and working up? Does it make a difference if I go down from the top or up from the bottom?

If you try the code you will see that it does in fact insert each time and not overwrite anything. Whenever you insert (or delete) multiple rows in a range, the standard practice is to go from the bottom up. This is because each insertion/deletion affects the length of the range on the sheet - but not in the code. As such, what was the last row initially will no longer be the last row after the first interaction.

 

[jolivanes]

Re: "I have a row of data in a spreadsheet"
Which row? Just any row? By selection? By inputbox? Hardcoded in macro?

 

[yomerves]

If you try the code you will see that it does in fact insert each time and not overwrite anything. Whenever you insert (or delete) multiple rows in a range, the standard practice is to go from the bottom up. This is because each insertion/deletion affects the length of the range on the sheet - but not in the code. As such, what was the last row initially will no longer be the last row after the first interaction.

That makes sense! Thanks for explaining.

 

[yomerves]

Not 100% certain of your intention (insert every time or overwrite) but give the following a try on a copy of your workbook, change the sheet names and row-to-copy as appropriate.

Option Explicit
Sub Copy_Row2_Every_20_Rows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")       '<~~ *** Change sheet name to suit ***
   
    Dim i As Long
    For i = 1000 To 20 Step -20
        ws.Rows(2).Copy                 '<~~ *** Change this to the row you want to copy ***
        Rows(i).Insert
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

This solution worked!

 

[kevin9999]

This solution worked!

Happy to help & thanks for the feedback