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.
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
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.
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
Read More: How to Copy and Paste Thousands of Rows in Excel (3 Ways)
- Copy and Paste in Excel and Keep Cell Size (7 Examples)
- How to Copy and Paste in Excel without Formulas (7 Easy Tricks)
- Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- Copy Rows in Excel with Filter (6 Fast Methods)
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.
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.
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.
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.
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
- Use VBA to Paste Values Only with No Formatting in Excel
- How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
- Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
- How to Autofilter and Copy Visible Rows with Excel VBA
- Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)