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

In this tutorial, we will demonstrate the methods to automatically copy rows in Excel to another sheet. Microsoft Excel does not provide any feature to copy rows automatically to another sheet. So, I have to go through multiple steps to copy rows from one sheet to another.

How to Copy Rows Automatically in Excel to Another Sheet: 4 Methods

To copy rows automatically in Excel to another sheet we can apply multiple methods. In this tutorial, we will explain 4 methods to copy rows to another sheet.

1. Automatically Copy Rows in Excel to Another Sheet Using Filters

First and foremost, we will use the filter option to copy rows automatically. In the following dataset, we will copy all rows from the data range (B4:D10) except the rows that have sales data for “New York” city.

Let’s go through the steps to perform this action.

STEPS:

• Firstly, select the data range (B4:D10).
• Then, select data range (B4:D10) > Data tab > Sort & Filter > Filter

• The above action makes the filter icon visible in the header of the data range.

• Secondly, click on the drop-down icon in cell D4.
• Check only the option New York and uncheck the rest of all.
• Press OK.

• The above command returns the filtered value of the selected data range. We can see only the rows having the value of city New York.

• Thirdly, select the data range (B4:D10) and press Alt +; to select all the visible cells in the filtered data range.
• Then do a right-click and from the popup menu select the option Copy or we can use the shortcut CTRL+C to copy the selected data range.

• Fourthly, go to another sheet named “Copy-Filter” and paste the copied data.

• After that, go back to the previous sheet, select cells (B5:D10), and delete them.

• So, we can see there is no data available in our worksheet now.

• Lastly, go to the Data tab and disable the filter option. So, we get the sales data of the city “New York” deleted from the data range.

NOTE:

This method is partially automatic. Because at one stage we have to copy and paste data manually. So, this method is not so feasible in real-time.

2. Combine IFERROR & VLOOKUP Functions to Copy Rows Automatically to Another Sheet in Excel

We can easily copy rows automatically in Excel by using the combination of the IFERROR with VLOOKUP functions. The IFERROR function is applied to intercept errors and returns a defined value if the formula in this function occurs error. VLOOKUP is an abbreviation for ‘Vertical Lookup’. It allows Excel to look for a specific value in a column so that it can return a value from another column in the same row. To illustrate this method we will continue with the same dataset that we used in the previous example.

So, let’s take a look at the steps to perform this action.

STEPS:

• First, go to the sheet “IFERROR &VLOOKUP(2)” where we want to paste our data.
• Next, select cell B4 and insert the following formula in that cell:

• Now, press ENTER. The above command returns the values of column B from the sheet “IFERROR & VLOOKUP” in sheet “IFERROR &VLOOKUP (2)”.

• Similarly, insert the following formulas in cells C4 and D4 of the worksheet “IFERROR &VLOOKUP (2)”.

In cell C4 insert the formula is:

In cell D4 insert the formula is:

• So, the above formulas will give you a dataset like the following image.

• After that, go back to the worksheet “IFERROR &VLOOKUP”. Add a new row in the data range like the highlighted one in the following image.

• Finally, open the worksheet “IFERROR &VLOOKUP(2)” Here we can see that the newly added row in the worksheet “IFERROR &VLOOKUP“ is duplicated into the worksheet “IFERROR &VLOOKUP(2)“ as seen above.

How Does the Formula Work?

• VLOOKUP(‘IFERROR &VLOOKUP’!$B$4:$B$1000,’IFERROR &VLOOKUP’!$B$4:$D$1000,1, FALSE), “”): This part retrieve the values from the first column of the worksheet ‘IFERROR &VLOOKUP‘.
• IFERROR(VLOOKUP(‘IFERROR &VLOOKUP’!$B$4:$B$1000,’IFERROR &VLOOKUP’!$B$4:$D$1000,1, FALSE), “”): Returns blank if any VLOOKUP value in the range ($B$4:$D$1000) gives an error.

3. Insert Table Query Window to Copy Rows Automatically in Excel to Another Sheet

In this example, we will use the “Table Query Window” to copy rows automatically to another sheet in Excel. To apply this method we will continue with our previous dataset. But for this method, we will use the table format of the data range like the following image.

So, let’s go through the steps associated with the method.

STEPS:

• In the beginning, select the entire table range (B4:D10).
• Then go to the Table Design tab and select the option Properties. We will rename the table as “New_Table”.

• Next, Select the option From Table/Range from the ribbon of the Data tab.

• Now a new window will appear for query settings.
• Go to the Add Column tab in that window. From the drop-down of the Index Column select the option From 1.

• So, the above action adds a new Index column with our existing data range.

• Next, go to the File Click on the option “Close & Load To”.

• One more dialogue box will appear. In that box check the option “New worksheet” to put the data in a new worksheet. Also, check the option “Add this data to the Data Model”.
• Then, press OK.

• The above commands return a new table in a new worksheet. We can see that the new worksheet’s name is the same as the table’s name.

• Then, go back to the worksheet “Table”. Add a new row in the data range like the highlighted one in the following image.

• After that, again go to the worksheet “NEW_TABLE”. Right-click on the name of the worksheet in the “Queries & Settings” section and click on “Refresh”.

• Lastly, we can see that if we add a new row in the table of the worksheet named “Table” it is automatically duplicated into the new worksheet that we get from “Power Query Editor”.

Read More: Copy and Paste Thousands of Rows in Excel

4. VBA Code to Copy Rows Automatically in Excel to Another Sheet

Using VBA code to copy rows automatically in Excel to another sheet is the most effective way. We will demonstrate 3 ways to apply VBA code to copy rows from a dataset.

4.1 Keep Source Data and Copy Rows Automatically to Another Sheet in Excel

We’ll use VBA code to copy rows to another sheet the first way. This method not only copies the rows to another sheet but also keeps the source data unchanged. In this example, we will copy the rows from the dataset given below.

So, let’s go through the steps to perform this action.

STEPS:

• Firstly, right-click on the sheet name from which we want to copy rows. Select the option “View Code”.

• A new blank VBA module will appear.
• Secondly, insert the following code in the blank window:

Sub copy_row_1()
Dim r1 As Range
Dim r2 As Range
Set r1 = ThisWorkbook.Sheets("VBA1").UsedRange
Set r2 = ThisWorkbook.Sheets("VBA1Copy").Range(r1.Address)
r2.Value = r1.Value
End Sub

• Then click on Run.
• This code will copy the rows from sheet “VBA1” to “VBA1Copy”. We can see the sheet names are highlighted in the below image.

• Lastly, go to the sheet “VBA1Copy”. We can see that in sheet “VBA1Copy” all of the rows from sheet “VBA1” are copied. If we go back to sheet “VBA1” we will see the source data is unchanged.

4.2 Automatically Copy Rows to Another Sheet and Remove from Source Data in Excel

In this method, the original value, however, will be removed after copying rows from a worksheet. We will copy only the rows in the dataset below that have the value “New York” in the column City.

Let’s look at the steps that go along with this method.

STEPS:

• First, select the data range (A1:C7).
• Next, right-click on the worksheet name “VBA2”. Click on the “View Code”

• Now, we can see a new blank VBA.
• Then, insert the following code in the blank window:

Sub copy_rows()
For Each cell In Selection.Columns(3).Cells
If cell.Value = "New York" Then
cell.EntireRow.Copy Worksheets("VBA2Copy").Range("A" & Rows.Count).End(3)(2)
cell.EntireRow.Delete
End If
Next
End Sub

• Click on Run.
• This code will copy the rows from sheet “VBA2” to “VBA2Copy”. We can see the sheet names are highlighted in the below image.

• After that, we can see that this action deletes the row from the sheet “VBA2” that has the value “New York” in the column City.

• Finally, if we go to sheet “VBA2Copy” we can see that all the rows with the value “New York” in column City are copied in that sheet.

4.3 Copy Rows in Excel to Another Sheet Dynamically

In this method, we will apply a VBA code in our previous dataset to copy rows. This time the code will group the values of column City by their name. Then it will copy the values with the same name to an individual worksheet.

So, let’s have a look at the steps involved in this method.

STEPS:

• Firstly, select the sheet Dynamic. Right-click on the sheet and select the option “View Code”.

• Now, we can see a new blank VBA.
• Secondly, insert the following code in the blank window:

Sub Copy_Rows_3()
Dim r1 As Range, Row_Last As Long, sht As Worksheet
Dim Row_Last1 As Long
Dim src As Worksheet
'Change this to the sheet with the data on
Set src = Sheets("Dynamic")
Row_Last = src.Cells(Cells.Rows.Count, "B").End(xlUp).Row
For Each r1 In src.Range("B5:B10" & Row_Last)
On Error Resume Next
Set sht = Sheets(CStr(r1.Value))
On Error GoTo 0
If sht Is Nothing Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CStr(r1.Value)
'Sheets(CStr(r.Value)).Cells(1, 1) = "Total"
Row_Last1 = Sheets(CStr(r1.Value)).Cells(Cells.Rows.Count, "B").End(xlUp).Row
src.Rows(r1.Row).Copy Sheets(CStr(r1.Value)).Cells(Row_Last1 + 1, 1)
Sheets(CStr(r1.Value)).Cells(1, 2) = WorksheetFunction.Sum(Sheets(CStr(r1.Value)).Columns(3))
Set sht = Nothing
Else
'Sheets(CStr(r.Value)).Cells(1, 1) = "Total"
Row_Last1 = Sheets(CStr(r1.Value)).Cells(Cells.Rows.Count, "B").End(xlUp).Row
src.Rows(r1.Row).Copy Sheets(CStr(r1.Value)).Cells(Row_Last1 + 1, 1)
Sheets(CStr(r1.Value)).Cells(1, 2) = WorksheetFunction.Sum(Sheets(CStr(r1.Value)).Columns(3))
Set sht = Nothing
End If
Next r1
End Sub

• Click on Run.

• This code will copy the rows from sheet “Dynamic” to individual sheets. We can see that the individual sheet is named after the values of column City.

• If we click on sheet “New York” we can see only the rows that have the value “New York” in this sheet.

• Then, click on sheet “Texas” we can see only the rows that have the value “Texas” in this sheet.

• Finally, click on sheet “Los Angeles”, we can see only the rows that have the value “Los Angeles” in this sheet.

Read More: Copy Rows from One Sheet to Another Based on Criteria

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

We’ve covered how to automatically copy rows in Excel to another sheet in this article. Hopefully, the previous examples have clarified the process to copy rows automatically. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond as soon as possible.

Related Articles

• Copy Every Nth Row in Excel
• Copy Alternate Rows in Excel
• Copy Rows in Excel with Filter
• Copy Excluding Hidden Rows in Excel

<< Go Back to Copy Rows | Copy Paste in Excel | Learn Excel