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.
'copy rows 6-7 and paste
'to existing rows 8-9
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.
Just copy and paste the following macros into the visual code editor and press F5 to run it.
'copy rows 12-13 and paste
'to new inserted rows 8-9
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.
Dim WSheet1 As Worksheet
Dim WSheet2 As Worksheet
Set WSheet1 = Worksheets("sheetA")
Set WSheet2 = Worksheets("sheetB")
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.
Dim Irng, Orng As Range
Set Irng = Application.InputBox( _
Prompt:="Rows no to copy", _
Set Orng = Application.InputBox( _
Prompt:="Rows no to paste copied rows", _
- 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.
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.
- How to Paste From Clipboard to Excel Using VBA
- How to Copy Visible Cells Only without Header Using VBA
- How to Use VBA PasteSpecial for Formulas and Formats in Excel
- Excel VBA to Copy Only Values to Destination
- How to Use VBA PasteSpecial to Keep Source Formatting in Excel
- How to Use VBA to Paste Values Only with No Formatting in Excel
- Excel VBA: Copy Cell Value and Paste to Another Cell
- How to Autofilter and Copy Visible Rows with Excel VBA
- VBA Paste Special to Copy Values and Formats in Excel