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

-
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
See more 

8 replies

Best answer
approved by Ratnendra Ashok on Jan 1, 2019
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488
4
Thank you
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

Say "Thank you" 4

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

CCM 6146 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
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488
0
Thank you
Hi,

Please, could you post a workbook example of your data on some shared sites like http://www.speedyshare.com/ 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.

http://www.speedyshare.com/files/24689240/TestFilePension.xls
aquarelle
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488 -
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
0
Thank you
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!
aquarelle
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488 -
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
-1
Thank you
Hi,

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

Thank you.
aquarelle
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488 -
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
-1
Thank you
Sorry, I tried it a few times, but nothing changes.

??
aquarelle
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488 -
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
-1
Thank you
Perfect! Thanks very much for your help. You have been very patient.

Take care.
aquarelle
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488 -
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?
-1
Thank you
aquarelle,
I am getting error 400 when I use this in excel 2007. Please help.
aquarelle
Posts
7239
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
October 31, 2019
488 -
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
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.
-1
Thank you
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!