Copy and Insert Row Multiple Times in Excel

If we want to duplicate the existing rows in a table in Excel when we do it manually, then it can be a time-consuming process as we need to insert and copy the values. We can automate this process using the VBA application.

Read this tutorial to learn how you can copy and insert a row multiple times, or duplicate the row "X" times, in Excel. Here we will first insert the VBA module and then run the code to complete the task. Let's look at a simple procedure for copying and inserting a row multiple times or duplicating a row "X" times in Excel.

Step 1

Let us consider an Excel sheet that has a table similar to the one below.

To open the VBA application, click on insert, select view code, click on insert, select module, and type the below-mentioned program1 into the text box as shown in the below image.

Program 1

Sub test()
'Update By Nirmal
    Dim xCount As Integer
LableNumber:
    xCount = Application.InputBox("Number of Rows", "Duplicate the rows", , , , , , 1)
    If xCount < 1 Then
        MsgBox "the entered number of rows is error, please enter again", vbInformation, "Select the values"
        GoTo LableNumber
    End If
    ActiveCell.EntireRow.Copy
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(xCount, 0)).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

Step 2

Now save the sheet as a macro-enabled worksheet, click the value you want to duplicate, then click F5, select the number of times you want to duplicate, and click OK.

If we want to duplicate the whole row, we can use program 2 instead of program 1

Program 2

Sub insertrows()
'Update By Nirmal
Dim I As Long
Dim xCount As Integer
LableNumber:
xCount = Application.InputBox("Number of Rows", "Duplicate whole values", , , , , , 1)
If xCount < 1 Then
MsgBox "the entered number of rows is error ,please enter again", vbInformation, "Enter no of times"
GoTo LableNumber
End If
For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 2 Step -1
Rows(I).Copy
Rows(I).Resize(xCount).Insert
Next
Application.CutCopyMode = False
End Sub

Conclusion

In this tutorial, we used a simple example to demonstrate how you can copy and paste a row or column multiple times in Excel.