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.


Download Practice Workbook

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


4 Examples to Copy Multiple Rows in Excel Using Macro

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.

copy multiple rows in Excel 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 multiple rows in Excel using Macro

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

copy multiple rows in Excel using Macro

Read More: How to Copy Rows in Excel (4 Easy Ways)


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.copy multiple rows in Excel using Macro

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

copy multiple rows in Excel using Macro

Read More: How to Copy and Paste Thousands of Rows in Excel (3 Ways)‌


Similar Readings


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.

copy multiple rows in Excel using Macro

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.

copy multiple rows in Excel using Macro

Read More: How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)


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.

copy multiple rows in Excel using Macro

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 copyinput box and hit

  • Then in the next input boxRows no to paste copied rows”, put 8:9 and hit

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

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

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria


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.

Conclusion

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


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo