How to Copy Multiple Rows in Excel Using Macro (4 Examples)

In this article, we’ll illustrate how to copy multiple rows in the same or different worksheets and paste them over existing rows or newly inserted rows in Excel using Macro.

How to Copy Multiple Rows in Excel Using Macro: 4 Examples

In this article, we’ll use the following sample dataset that describes the working schedule for an employee for 5 days a week. We are going to make some changes to this schedule on-demand by copying multiple rows by using macro.

Write Code in Visual Basic Editor
Follow the steps to open the visual basic editor and write some code there.

Go to the Developer tab from the Excel ribbon.
Click the Visual Basic option.

In the Visual Basic For Applications window, click the Insert dropdown to select the New Module

Now that a new module is opened, write some code there and press F5 to run.

1. Copy Multiple Rows and Paste Over Existing Rows in Excel Using Macro

In this example, we will change the working schedule for the next week where the employee will have day shift duties on all 5 working days. To do this, we’ll copy rows 6-7 to existing rows 8-9 by running a simple macro in the visual code editor.

Copy and paste the code below and run it to accomplish the task above.

Sub CopyMultipleRows()
'copy rows 6-7 and paste
'to existing rows 8-9
Range("6:7").Copy Range("8:9")
End Sub

2. Insert New Rows to Paste the Copied Multiple Rows Using Macro

In this example, we want to add two more extra working days for the employee for a particular week. And these two working days in the IT department will follow the first 3 days in the Security department. So clearly we need to insert two new rows after row 7 and paste rows 12-13 there to accomplish the task.

Just copy and paste the following macros into the visual code editor and press F5 to run it.

Sub CopyMultipleRows()
'copy rows 12-13 and paste
'to new inserted rows 8-9
Range("12:13").Copy
Range("8:9").Insert
End Sub

3. Copy Multiple Rows to Another Worksheet Using Macro

With this example, we’re going to show how to copy multiple rows from one worksheet to another worksheet using macro in Excel. Here we have our dataset in sheetA with a working schedule of an employee.

And the following screenshot shows sheetB with no data.

Now copy and paste the following code and run by pressing F5 to copy the rows 4-9 of sheetA to rows 4-9 of sheetB.

Sub CopyRowsToAnotherSheet()
Dim WSheet1 As Worksheet
Dim WSheet2 As Worksheet
Set WSheet1 = Worksheets("sheetA")
Set WSheet2 = Worksheets("sheetB")
WSheet1.Range("4:9").Copy WSheet2.Range("4:9")
End Sub

Here is the output.

4. Using Macro to Copy Multiple Rows Based on User Input in Excel

We can use Excel’s InputBox function to take user input on what the rows number to copy and where to paste them. Let’s say we want to solve the task described in example 2 where we added two extra working days for the employee. We need to copy rows 12-13 and paste them into inserted rows 8-9. This is our dataset in the following screenshot.

Follow the simple steps:

Now put the following code in the visual code editor and press F5 to run.

Sub InsertRowsfromUserInput()
Dim Irng, Orng As Range
   Set Irng = Application.InputBox( _
      Prompt:="Rows no to copy", _
      Type:=8)
   Set Orng = Application.InputBox( _
      Prompt:="Rows no to paste copied rows", _
      Type:=8)
Irng.Copy
Orng.Insert
End Sub

Put 12:13 in the “Rows no to copy” input box and hit

Then in the next input box “Rows no to paste copied rows”, put 8:9 and hit

In the following screenshot, we can see the final output.

Things to Remember

If the no of copied rows should be the same as the no of rows to paste to get the desired results.
In the last example, we used the Range Object to get the rows no to copy and paste from the input box.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Copy Multiple Rows Using Macro.xlsm

Conclusion

Now, we know how to copy multiple rows in Excel using macro with 4 different examples. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.