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.


Download Practice Workbook

You can download the practice workbook from here.


4 Methods to Copy Rows Automatically in Excel to Another Sheet

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.

Automatically Copy Rows in Excel to Another Sheet Using Filters

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

STEPS:

  • Firstly, select the data range (B4:D10).
  • Then got to the Data From the drop-down of the “Sort & Filter” option select the option “Filter”.
  • In short: Select data range (B4:D10) > Data tab > Sort & Filter > Filter

Automatically Copy Rows in Excel to Another Sheet Using Filters

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

Automatically Copy Rows in Excel to Another Sheet Using Filters

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

Automatically Copy Rows in Excel to Another Sheet Using Filters

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

Automatically Copy Rows in Excel to Another Sheet Using Filters

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

Automatically Copy Rows in Excel to Another Sheet Using Filters

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

Read More: How to Copy Rows in Excel with Filter (6 Fast Methods)


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 IFERROR & 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.

Combine IFERROR & VLOOKUP Functions to Copy Rows Automatically in Excel

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:
=IFERROR(VLOOKUP('IFERROR &VLOOKUP'!$B$4:$B$1000,'IFERROR &VLOOKUP'!$B$4:$D$1000,1, FALSE), "")

Combine IFERROR & VLOOKUP Functions to Copy Rows Automatically in Excel

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

Combine IFERROR & VLOOKUP Functions to Copy Rows Automatically in Excel

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

In cell C4 insert the formula is:

=IFERROR(VLOOKUP('IFERROR &VLOOKUP'!$B$4:$B$1000,'IFERROR &VLOOKUP'!$B$4:$D$1000,2, FALSE), "")

In cell D4 insert the formula is:

=IFERROR(VLOOKUP('IFERROR &VLOOKUP'!$B$4:$B$1000,'IFERROR &VLOOKUP'!$B$4:$D$1000,3, FALSE), "")
  • So, the above formulas will give you a dataset like the following image.

Combine IFERROR & VLOOKUP Functions to Copy Rows Automatically in Excel

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

Read More: How to Copy Rows in Excel (4 Easy Ways)


Similar Readings


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.

Insert Table Query Window to Copy Rows Automatically to Another Sheet

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

Insert Table Query Window to Copy Rows Automatically to Another Sheet

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

Insert Table Query Window to Copy Rows Automatically to Another Sheet

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

Insert Table Query Window to Copy Rows Automatically to Another Sheet

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

Insert Table Query Window to Copy Rows Automatically to Another Sheet

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

Insert Table Query Window to Copy Rows Automatically to Another Sheet

  • 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 the “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: Excel VBA to Copy Rows to Another Worksheet Based on Criteria


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.

Keep Source Data and Copy Rows to Another Sheet

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

Keep Source Data and Copy Rows to Another Sheet

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

Keep Source Data and Copy Rows to Another Sheet

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

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


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.

Copy Rows to Another Sheet and Remove from Source Data

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”

Copy Rows to Another Sheet and Remove from Source Data

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

Copy Rows to Another Sheet and Remove from Source Data

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

Read More: How to Autofilter and Copy Visible Rows with Excel VBA


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.

Copy Rows in Excel to Another Sheet Dynamically

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

Copy Rows in Excel to Another Sheet Dynamically

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

Copy Rows in Excel to Another Sheet Dynamically

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

Copy Rows in Excel to Another Sheet Dynamically

  • 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 and Paste Values to Next Empty Row with Excel VBA (3 Examples)


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

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

15 Comments
  1. Is there a way to do this for multiple values to go to a different tab?

    Example: I have a list of employees with different types of employment; Federal, Casual Hire, Contract, Forest Service, etc.

    I have my main sheet (Master), I have column E that classifies which type of employee they are, I want that row to copy to a new tab depending on which group they are in.

    In other words, I would like a tab for Federal with all the federal from column E to copy into it, a tab for casual hire with all the casual hire from column E, etc.

    Thank you for any help you can give

  2. Hello DESTINY,
    First, thanks for your curious question. It was amusing to solve the problem. Let me guide you to fulfill your query.

    Step 1. Assume you have a Dataset where you have the Names of the employees in one column and the types of the employees in another.
    1

    Step 2. Then insert the following code in the VBA 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, “C”).End(xlUp).Row
    For Each r1 In src.Range(“C5:C13” & 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

    N.B. if you are following our article, use the VBA code under the method “Copy Rows in Excel to Another Sheet Dynamically” and change the marked portions.

    2

    Step 3. After pressing Run, you will get the result in individual desired cells.

    3

    4

    Thanks

  3. Good morning,

    I am trying to update an existing excel file to make it a bit more efficient. At this point I am having to copy, paste and assort data from an Access file. I am looking to take the data from the Access file, place a copy of it on the source sheet, and then have excel automatically put a copy of each row on their appropriate sheets based on the plant listed on column “L”.

    I have downloaded the example excel file and attempted the example/instructions you give in 4.2. However, when I try this on either my excel or yours I get the errors code ‘1004’

    If you have any idea what is causing this and it’s solution, I would sincerely appreciate your help. Thanks

  4. Hello MICHAEL,
    Thanks for the amazing question. Let me guide you in solving this problem.

    If you want to solve this problem at first you have to know the possible reason behind this problem.
    The first reason can be MACRO NAME ERROR.
    This problem occurs if VBA code is used but the file has not been saved as ‘Macro-Enabled Workbook’.
    The second reason can be FILE CONFLICT.
    This problem occurs when the file has many codes and somehow they get conflicted with each other.
    Another reason can be “TOO MUCH LEGEND ENTRIES”
    If the excel chart has more legend entries than the space available then this problem occurs.
    The last possible reason can be “EXCEL FILE CORRUPTION”
    In this case, the whole file got corrupted or damaged, or infected.

    Though many reasons can cause this error 1004 problem luckily we can fix it with the help of some easy methods.

    Solution 1. Deleting GWXL97.XLA Files
    Step 1. Go to C:\Program Files\MS Office\Office\XLSTART.
    Step 2. Find GWXL97.XLA file and delete it.
    Step 3. After deleting the file, reopen the excel file and check if the problem is solved or not.

    Solution 2. Check Trust Access
    Step 1. Open the excel file.
    Step 2. Click on the Files option and press Option.
    Step 3. Go to the ‘Trust Center Option’ and enter into the ‘Trust Center Settings’ option.
    Step 4. After that go to the ‘Developer Macro Settings’ and click the tick option and check if the problem is solved or not.

    Solution 3. Create Another ‘Macro-Enabled Workbook Template’
    Step 1. Open a new workbook and entry the data as before.
    Step 2. Go to File>Save As options.
    Step 3. In the File name section, select the Excel Macro-Enabled Workbook option.
    Step 4. Save the file with the desired name.

    These solutions should solve your problem. If you face any further problems, please let us know so that we can help you. Thanks.

  5. Hi Rian,

    I am able to run the code but it is only fetching first row and created sheet but not populating the second row. its giving run time error 1004

  6. I have more complicated situation. I’m not sure it’s even possible to do but maybe you can help me. I have 1 Sheet full of data and 2 sheet full of data. I want to merge these sheets, so transfer data from one sheet to another. But the transfer should happened into the specific rows. For example:
    Data from Sheet 1 (A10072:AT10801) has to go to Sheet 2 (A10772:AT10801) and data from Sheet 1 (A10802:AT10831) has to go to Sheet 2 (A10802:AT10831). Simple formula and clicking and dragging would not work as its too much time wasted. Maybe you have a solution for me

    • Reply
      Osman Goni Ridwan Sep 12, 2022 at 9:29 AM

      Hello JUSTINA!
      I have an easy solution to your problem. To copy data of range Sheet1 (A10072:AT10801) to range Sheet 2 (A10772:AT10801), you can use this VBA Code.

      Sub copy_to_another_worksheet()
      Worksheets("Sheet1").Range("A10072:AT10801").Copy _
      Worksheets("Sheet2").Range("A10772:AT10801")
      End Sub

      And to copy data of range Sheet 1 (A10802:AT10831) to the range Sheet 2 (A10802:AT10831), use the following VBA code-

      Sub copy_to_another_worksheet()
      Worksheets("Sheet1").Range("A10802:AT10831").Copy _
      Worksheets("Sheet2").Range("A10802:AT10831")
      End Sub

      I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  7. Hello!

    I was successful in using your instructions here- 4.3 Copy Rows in Excel to Another Sheet Dynamically to have my data filter to In-Progress or Complete tabs. Two problems and/or questions you may be able to assist with.

    1. Is there anyway to make this process not duplicate data which has already copied over. For example. If I add a row of data to the dynamic tab and run the process it copies overall all the data not just the new data added.

    2. I was trying to also add separate code or logic to the In Progress tab that if Column M changes to complete it would delete it from this tab and move it to the Complete tab. I can’t seem to get this to work even when using your notes here 4.2 Automatically Copy Rows to Another Sheet and Remove from Source Data in Excel. I am not getting an error message, simply nothing happens.

    Any help to these 2 issues would be greatly appreciated!

  8. Hello CALEY FORBES,
    Thanks for the amazing question. Let me guide you in solving this problem.

    First, I want to solve your first query. I think to solve your problem it is better to use our first method ‘Automatically Copy Rows in Excel to Another Sheet Using Filters’ method than using VBA code. The reason behind this is I think the first method will do your desired work without hesitation.

    For the second query, you have to insert the following formula in the VBA windows.

    Sub Cut_Range_To_Clipboard()
    Range(“B4:B10”).Cut ‘This will cut the source range and copy the Range “B4:B10” data into Clipboard
    ‘Now you can select any range and paste there
    Range(“J2”).Select
    ActiveSheet.Paste
    End Sub

    Note: in the Range section you can change the desired option to paste accordingly.

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]
    Happy Excelling!!!

  9. Hello,

    I have tried to use the code for Copying Rows in Excel to Another Sheet Dynamically and it is working well except that it creates a blank sheet and getting an error code 400. Are there other variables/ranges that I need to adjust?

    Regards,

  10. Hello again,

    Just to add to my question, I am getting an error on the line below.

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CStr(r1.Value)

    Regards,

    • Reply
      Naimul Hasan Arif Oct 11, 2022 at 7:32 PM

      Thanks XAVIER for your correction.

      Actually, there is just a little mistake in the code. Instead of writing B5:B, it’s been written B5:B10. This correction has worked perfectly for me.


      You can submit more problems to us at [email protected]. Regards!

  11. Is there a way to make this code only copy values and not formulas?

    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)
    End If
    Next
    End Sub

    • Hello, Rick!
      Thanks for sharing your problem with us!
      While copying, you have to copy the whole thing (values + formulas). You can have the values (without any formula) only while pasting the copied portion into another place.
      While pasting, instead of using ‘.Paste‘ to replicate a formula result as a value rather than the formula itself, use ‘.PasteSpecial‘.

      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).PasteSpecial xlPasteValues
      End If
      Next

      Hope this will help you!
      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy
      .

Leave a reply

ExcelDemy
Logo