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

Get FREE Advanced Excel Exercises with Solutions!

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.

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: Excel VBA: Copy Row If Cell Value Matches


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: Copy and Paste Values to Next Empty Row with Excel VBA


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


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


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.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo