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

Method 1 – Automatically Copy Rows in Excel to Another Sheet Using Filters

In the following dataset, we will copy all rows from the data range (B4:D10) except the rows that contain New York in the City cell.

Steps:

• Select the entire data range (B4:D10).
• Go to the Data tab and choose Sort & Filter, then select Filter.

• The filter icons are now visible in the headers of the data range.

• Click on the drop-down icon in cell D4.
• Check only the option New York and uncheck the rest.
• Press OK.

• We can see only the rows with New York.

• Select the filtered data range (B4:D10) and press Alt +; to select all the visible cells.
• Right-click and select the option Copy or use the shortcut Ctrl + C to copy the selected data range.

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

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

• There is no data visible in our worksheet now.

• Go to the Data tab and disable the filter option. We got the sales data of the city “New York” deleted from the data range.

Method 2 – Combine IFERROR and VLOOKUP Functions to Copy Rows Automatically to Another Sheet in Excel

We will continue with the same dataset that we used in the previous example.

Steps:

• Go to the sheet “IFERROR &VLOOKUP(2)” (from our sample workbook) to paste the data.
• Select cell B4 and insert the following formula in that cell:

• Press Enter. The above command returns the values of column B from the sheet “IFERROR & VLOOKUP” in sheet “IFERROR &VLOOKUP (2)”.

• In cell C4, insert the formula:

• In cell D4, insert the formula:

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

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

• Open the worksheet “IFERROR &VLOOKUP(2)”.
• The newly added row in the worksheet “IFERROR &VLOOKUP“ is duplicated into the worksheet “IFERROR &VLOOKUP(2)”.

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.

Method 3 – Insert a Table Query Window to Copy Rows Automatically in Excel to Another Sheet

We will continue with our previous dataset. However, we will use the table format of the data range like in the following image.

Steps:

• Select the entire table range (B4:D10).
• Go to the Table Design tab and select the option Properties. Rename the table as “New_Table”.

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

• 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.

• This adds a new Index column in the existing data range.

• Go to File.
• Click on the option “Close & Load To”.

• One more dialogue box will appear. Check the option “New worksheet” to put the data in a new worksheet.
• Check the option “Add this data to the Data Model”.
• Press OK.

• This returns a new table in a new worksheet. The new worksheet’s name is the same as the table’s name.

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

• Go to the worksheet “NEW_TABLE”.
• Right-click on the name of the worksheet in the “Queries & Settings” section and click on “Refresh”.

• If we add a new row in the table of the worksheet named “Table”, it is automatically duplicated into the new worksheet.

Read More: Copy and Paste Thousands of Rows in Excel

Method 4 – VBA Code to Copy Rows Automatically in Excel to Another Sheet

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

We will copy the rows from the dataset given below.

Steps:

• Right-click on the sheet name from which you want to copy rows. Select the option “View Code”.

• A new blank VBA module will appear.
• 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

• Click on Run.
• This code will copy the rows from sheet “VBA1” to “VBA1Copy”. The sheet names are highlighted in the screenshot.

• Go to the sheet “VBA1Copy”. All of the rows from sheet “VBA1” are copied. If you go back to sheet “VBA1”, the source data is unchanged.

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

We will copy only the rows in the dataset below that have the value “New York” in the column City and remove it from the dataset.

Steps:

• Select the data range (A1:C7).
• Right-click on the worksheet name.
• Click on View Code.

• You’ll get a blank VBA.
• Insert the following code in the 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”.The sheet names are highlighted in the screenshot.

• This deletes the row from the sheet “VBA2” that has the value “New York” in the column City.

• If you go to sheet “VBA2Copy”, all the rows with the value “New York” in column City are moved in that sheet.

Case 4.3 Copy Rows in Excel to Another Sheet Dynamically

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.

Steps:

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

• You’ll get a new blank VBA.
• Insert the following code in the 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. The individual sheet is named after the values of column City.

• If you click on sheet “New York”, only the rows that have the value “New York” are in this sheet.

• Cick on sheet “Texas”, and you’ll get only the rows that have the value “Texas” in this sheet.

• Click on sheet “Los Angeles” to get rows that have the value “Los Angeles” in the sheet.

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

Download the Practice Workbook

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