Copy and insert rows and number of times

Solved/Closed

Excel

Pabs - 12 Oct 2010 à 17:08
Duck - 18 Nov 2014 à 13:20

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

Excel duplicate row x times
How to duplicate cells in excel multiple times - Best answers
Excel duplicate every row x times - Best answers
Saints row 2 cheats - Guide
Delete duplicates in excel - Guide
Excel mod apk for pc - Download - Spreadsheets
Excel duplicate sheet - Excel Forum
How to duplicate data from one excel sheet to another ✓ - Excel Forum

8 responses

Réponse 1 / 8

aquarelle Posts 7181 Registration date Saturday 7 April 2007 Status Moderator Last seen 14 February 2026 491
12 Oct 2010 à 17:46

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
13 Oct 2010 à 13:09

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
10 Jun 2014 à 08:54

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

Réponse 2 / 8

aquarelle Posts 7181 Registration date Saturday 7 April 2007 Status Moderator Last seen 14 February 2026 491
13 Oct 2010 à 13:36

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
14 Oct 2010 à 08:38

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 7181 Registration date Saturday 7 April 2007 Status Moderator Last seen 14 February 2026 491
14 Oct 2010 à 11:12

Hi,

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

See you

Réponse 3 / 8

pablodiablo Posts 3 Registration date Thursday 14 October 2010 Status Member Last seen 14 October 2010
14 Oct 2010 à 15:10

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 7181 Registration date Saturday 7 April 2007 Status Moderator Last seen 14 February 2026 491
14 Oct 2010 à 15:21

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
29 Feb 2012 à 09:09

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

jaw
14 Mar 2014 à 10:56

This is brilliant

Duck
18 Nov 2014 à 13:20

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

Réponse 6 / 8

pablodiablo Posts 3 Registration date Thursday 14 October 2010 Status Member Last seen 14 October 2010
14 Oct 2010 à 16:19

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

Take care.

aquarelle Posts 7181 Registration date Saturday 7 April 2007 Status Moderator Last seen 14 February 2026 491
14 Oct 2010 à 16:22

Always happy when I'm able to help.

Take care too :)

ibrauns
3 May 2011 à 10:44

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?

Réponse 7 / 8

uma
26 Oct 2010 à 12:49

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

aquarelle Posts 7181 Registration date Saturday 7 April 2007 Status Moderator Last seen 14 February 2026 491
27 Oct 2010 à 05:48

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
27 Oct 2010 à 15:35

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!