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

Pabs - Oct 12, 2010 at 05:08 PM - Latest reply:  Duck
- Nov 18, 2014 at 01:20 PM
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.

See more 

22 replies

Best answer
pablodiablo 3 Posts Thursday October 14, 2010Registration date October 14, 2010 Last seen - Oct 14, 2010 at 03:10 PM
1
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!

Thank you, pablodiablo 1

Something to say? Add comment

CCM has helped 1751 users this month

aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 14, 2010 at 03:21 PM
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
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 12, 2010 at 05:46 PM
0
Thank you
Hi,

Look at this thread : http://ccm.net/forum/affich-107673-copy-row-and-insert-n-times

Best regards
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
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 13, 2010 at 01:36 PM
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 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 14, 2010 at 11:12 AM
Hi,

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

See you
0
Thank you
Hi,

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

Thank you.
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 14, 2010 at 01:10 PM
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?
pablodiablo 3 Posts Thursday October 14, 2010Registration date October 14, 2010 Last seen - Oct 14, 2010 at 03:32 PM
0
Thank you
Sorry, I tried it a few times, but nothing changes.

??
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 14, 2010 at 03:42 PM
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
pablodiablo 3 Posts Thursday October 14, 2010Registration date October 14, 2010 Last seen - Oct 14, 2010 at 04:19 PM
0
Thank you
Perfect! Thanks very much for your help. You have been very patient.

Take care.
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 14, 2010 at 04:22 PM
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?
0
Thank you
aquarelle,
I am getting error 400 when I use this in excel 2007. Please help.
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Oct 27, 2010 at 05:48 AM
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
realiser 565 Posts Thursday May 14, 2009Registration date November 3, 2010 Last seen - Oct 27, 2010 at 03:35 PM
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.
0
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!