Copy and insert rows and number of times [Solved/Closed]

Report
-
 Duck -
Hello,

I have a sheet with over 800 rows. I need to copy each row and insert/repeat each entry 4 times below the preceding row.

Example:

Before

name1
name2
name3


After

name1
name1
name1
name1
name2
name2
name2
name2
name3
name3
name3
name3

I'd appreciate it very much any help I can get with this.

Thank you.

System Configuration: Windows XP / Safari 534.3

8 replies

Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Hi,

Look at this thread or try this macro:


Sub InsertRows()
Dim I As Long, J As Integer, Nb As Integer

For I = Range("A65536").End(xlUp).Row To 2 Step -1

Nb = 4

For J = 1 To Nb - 1
Rows(I + J).Insert xlDown
Rows(I).Copy
Rows(I + J).PasteSpecial '
Next

Next

Range("A1").Select
Application.CutCopyMode = False
End Sub



Best regards
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Thank you for your response aquarelle. Unfortunately I was not able to find a satisfactory response in the link you provided. I'm not versed in Excel Macros so perhaps I'm missing some good information here.

Help please?

Thank you.
hey there its not my code but just use it

Sub Copy_Row()
'Written by Barrie Davidson
Dim NRow As Integer
Dim CurrentRow As Integer
Dim SheetName As String
Dim Datasheet As String

Datasheet = ActiveSheet.Name
ActiveWorkbook.Sheets.Add after:=Sheets(Datasheet)
SheetName = ActiveSheet.Name
Sheets(Datasheet).Select
Range("A1").Select
Do Until Selection.Value = ""
CurrentRow = Selection.Row
NRow = InputBox("Current row selected is " & CurrentRow & Chr(13) & _
"Enter Number of Copies Required")
Selection.EntireRow.Copy
Sheets(SheetName).Select
ActiveCell.Range("A1:A" & NRow).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A" & NRow).Offset(1, 0).Select
Sheets(Datasheet).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Hi,

Please, could you post a workbook example of your data on some shared sites like https://authentification.site and copy/past the upload link in your next message.

Best regards
Hi. Yes here is the link to download a sample file. The goal is to copy each row and repeat it 3 or 4 times below before the next row of new data.

Thanks very much for your help.

https://authentification.site/files/24689240/TestFilePension.xls
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Hi,

I need to know if the SSN number is always different for each person ?

See you
Posts
3
Registration date
Thursday October 14, 2010
Status
Member
Last seen
October 14, 2010

Hi,

I will be copying each entire row. The macro will be used one time each time a new spreadsheet need to be formatted in this way.

Thank you!
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Right, then try this macro:
Sub InsertRows() 
    Dim I As Long, J As Integer, Nb As Integer 
    
    For I = Range("A65536").End(xlUp).Row To 2 Step -1   
        
             Nb = 4 
        
        For J = 1 To Nb - 1          
            Rows(I + J).Insert xlDown    
            Rows(I).Copy                      
            Rows(I + J).PasteSpecial    ' 
        Next 
        
    Next 
  
    Range("A1").Select 
    Application.CutCopyMode = False 
End Sub 

Whoever wrote this is a genius and saved me so much time! Thanks!
This is brilliant
This is what I needed.. I figured out that if you change Nb =5. you get 5 copies of the number
Hi,

Yes, the SSN is a unique number for each individual. It is also used as an identifier.

Thank you.
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
An other thing, do you want to copy the entire row or just certain cells of the row ?
Will you use this macro just one time in the workbook?
Posts
3
Registration date
Thursday October 14, 2010
Status
Member
Last seen
October 14, 2010

Sorry, I tried it a few times, but nothing changes.

??
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Here is your workbook with the macro. To run the macro press the Ctrl key and i key at the same time.
http://www.cijoint.fr/cjlink.php?file=cj201010/cijZ8soiab.xls
Posts
3
Registration date
Thursday October 14, 2010
Status
Member
Last seen
October 14, 2010

Perfect! Thanks very much for your help. You have been very patient.

Take care.
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Always happy when I'm able to help.

Take care too :)
Hi I am trying to do something simliar but the difference is. I want to isolate a row and have that row copy a certain number of times. I don't want the macro to go through the entire sheet. Sometimes I might have this row copied 5 times and sometimes 1 time, etc... Is this an easy tweak to what you have here already?
aquarelle,
I am getting error 400 when I use this in excel 2007. Please help.
Posts
7081
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 25, 2020
499
Hi uma,

I'm sorry but I don't know why you have this error because I haven't excel 2007. Maybe someone else could help you.

Best regards
Blocked Profile
HI Uma, please provide the complete error message for there's a possibility that there is an add on that has not been properly installed or still it might be that there's some missing files or corrupted ones that is causing this particular difficulty.
Hey Guys,

I have a similar yet peculiar problem. I have a sheet of 1500 rows and I need to add 4 rows underneath each existing row. No experience in using Macros, could anyone please help me with the code and how to implement it?

Thanks in advance!