Copy and insert rows and number of times

Solved/Closed

Excel

Pabs - Updated on Jan 1, 2019 at 10:04 AM
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.

System Configuration: Windows XP / Safari 534.3

How to duplicate rows in excel x times
Insert copied cells multiple times - Best answers
Excel duplicate row x times - Best answers
Number to words in excel - Guide
How to take screenshot in excel - Guide
You have guessed too many times ✓ - WhatsApp Forum
How to change time in whatsapp - Guide
How to change author in excel - Guide

8 responses

Answer 1 / 8

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Updated on Jan 1, 2019 at 10:07 AM

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

Pabs
Oct 13, 2010 at 01:09 PM

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.

himanshu
Jun 10, 2014 at 08:54 AM

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

Answer 2 / 8

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Oct 13, 2010 at 01:36 PM

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

Pabs
Oct 14, 2010 at 08:38 AM

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

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Oct 14, 2010 at 11:12 AM

Hi,

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

See you

Answer 3 / 8

pablodiablo Posts 3 Registration date Thursday October 14, 2010 Status Member Last seen October 14, 2010
Oct 14, 2010 at 03:10 PM

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 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
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

C
Feb 29, 2012 at 09:09 AM

Whoever wrote this is a genius and saved me so much time! Thanks!

jaw
Mar 14, 2014 at 10:56 AM

This is brilliant

Duck
Nov 18, 2014 at 01:20 PM

This is what I needed.. I figured out that if you change Nb =5. you get 5 copies of the number

Answer 5 / 8

pablodiablo Posts 3 Registration date Thursday October 14, 2010 Status Member Last seen October 14, 2010
Oct 14, 2010 at 03:32 PM

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

??

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
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

Answer 6 / 8

pablodiablo Posts 3 Registration date Thursday October 14, 2010 Status Member Last seen October 14, 2010
Oct 14, 2010 at 04:19 PM

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

Take care.

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Oct 14, 2010 at 04:22 PM

Always happy when I'm able to help.

Take care too :)

ibrauns
May 3, 2011 at 10:44 AM

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?

Answer 7 / 8

uma
Oct 26, 2010 at 12:49 PM

aquarelle,
I am getting error 400 when I use this in excel 2007. Please help.

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
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

Blocked Profile
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.

Similar discussions

Copy row and insert n times

How to duplicate rows x number of times

Excel VBA copy and paste loop with logic condition

Duplicate rows based on cell value

Excel - To generate unique ID

Excel - Repeat rows to a specific number of times

Similar discussions

Subscribe To Our Newsletter!