Copy Rows from One Sheet to Another Based on Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel sometimes we need to copy rows from any worksheet to another worksheet based on different criteria. In this article, I will show various ways to copy rows from one sheet to another based on criteria in Excel.


Watch Video – Copy Rows from One Sheet to Another Based on Criteria in Excel


Copy Rows from One Sheet to Another Based on Criteria in Excel: 6 Ways

You will discover six really simple methods for copying rows in Excel. They are all quite useful to utilize. You can choose any of these to complete your work. Without further ado, let’s examine each of them individually to copy rows from one sheet to another based on criteria in Excel.


1. Use of Excel Filter Option to Copy Rows from One Sheet to Another

For demonstrating this process let’s consider a dataset of some fruits with their unit price, weight, and total price. This full table is saved on a sheet name Filter Op. in our workbook. Now, we will copy the rows from Filter Op. and will save them on the Result1 sheet, using the Filtering and Copying options.

excel copy rows from one sheet to another based on criteria

STEPS:

  • Firstly, select the data.
  • Next, go to the Data tab from the ribbon.
  • Then, click on the Filter option, under Sort & Filter group.

excel copy rows from one sheet to another based on criteria

  • Select the desired column to which you are going to copy the rows. Here, I have selected the Fruits column.
  • Then, select the row which you want to copy from this Worksheet. I have selected the Mango row here for the example. Only the selected item’s rows will be copied. If you need to copy all the rows of this column you may select Select All options.

  • Copy the entire data by selecting the data and pressing Ctrl+C.

excel copy rows from one sheet to another based on criteria

  • Further, create a new worksheet by clicking on the + (plus) sign below or you can use the keyboard shortcut SHIFT+F11.
  • Paste the copied data by pressing Ctrl+V, into the new worksheet Result1.
  • Finally, all the selected data will be copied from Filter Op. to Result1.

excel copy rows from one sheet to another based on criteria


2. Utilize Advanced Feature to Duplicate Rows from One Sheet to Another

For showing this process we will consider the same example shown in method 1. But here I will use the Advanced Filter for copying the rows from Sheet3 to Sheet4. Our testing criteria will be all the fruits whose total price is greater than 150. So, we will copy the rows from Sheet3 to Sheet4 where the total price is greater than 150.

STEPS:

  • To begin with, go to the Result1.1 sheet and select Advanced under the Data tab.

  • After that, select Copy to another location option.

  • Then, choose the List range box go to the Advanced sheet, and copy the full dataset.

  • Further, pick the Criteria range cell.

  • Now, select the Copy to option which will shift automatically to the Result1.1 sheet, and select any cell of that worksheet.

  • Next, press the Ok button.

excel copy rows from one sheet to another based on criteria

  • Thus, rows will be copied from sheet Advanced to Result1.1 based on mentioned criteria.


3. Copy Rows from One Sheet to Another Using Array Formula

Another way of copying rows from one sheet to another is to use array formulas. Using array formulas, we will be able to automate the copy process. For showing this process let’s think about the same dataset above with another extra column which is Shop Names. Now we will copy the rows from one to another according to their Shop Names. Also, all the worksheets will be named as their Shop Name.

Our target will be to copy the rows according to their Shop Names to a new worksheet.

STEPS:

  • In the beginning, create new worksheets with the Shop Names.

  • Then, go to any new worksheet like Rooted. Then select cell B4 and enter the below formula then press CTRL+SHIFT+ENTER. If you are using MS Excel 365 then just press Enter.
=IFERROR(INDEX(Sheet7!$A$4:$E$100,SMALL(IF(Sheet7!$F$4:$F$100=$F$3,ROW(Sheet7!$A$4:$B$100)-ROW(Sheet7!$B$4)+1),ROWS(Sheet7!$A$4:$B4)),COLUMN()),"")

Formula Explanation

In this formula, we have used some Excel functions. Here I will discuss all the functions which were used in the array formula.

ROWS(array)

The ROWS function takes an array or a reference to a range of cells and returns the number of rows in a reference or array based on our given range.

COLUMN([reference])

By bypassing the cell reference in the parameter of the COLUMN function we will get the specific column number according to our given cell reference.

SMALL(array, n)

Using the SMALL function we are able to determine the nth smallest value in any specific array. Generally, the first portion of the parameter holds the range of the dataset or array and the other portion contains the desired position in the array or range of data to return from this function.

INDEX(array, row_num, [column_num])

Another function we have used is the INDEX function. There are parts in this function’s parameter. In the first portion, it takes an array or the range of our desired data range which is required. Secondly, it takes the row number from where we want to return the value. Lastly, the column number portion is not required, it’s optional. We can pass any specific column number value in an array from which to return a value.

IFERROR(value, value_if_error)

This is the last function which is the IFERROR function or the outermost function which was used in our array formula. This is mainly a conditional function that checks whether our given value is equal to an error value or not. In the first portion, it takes the inputted value and checks the value if the value is an error value then it returns the second portion of the parameter.

  • Further, copy the formula down and right to get the matched rows.

  • Do the same way for other worksheets.

  • You can check by changing any value in the Array Formula sheet whether the value is automatically changing or not.

excel copy rows from one sheet to another based on criteria

  • Go to the Rooted worksheet and see the changes.

excel copy rows from one sheet to another based on criteria


4. Using Combined Functions to Duplicate Rows

Let’s see another way to copy rows shown in another worksheet automatically. For this method, we will consider the same dataset used in method 4. Now we will sort the list of the fruits according to their Shop Names and check if the Total Price is greater than $130. If we just select any Shop Name from the drop-down list and press Enter then all the matched rows will be copied from the Functions sheet and shown in the Result2 sheet.

STEPS:

  • Firstly, Select the G5 cell in the Functions sheet and enter the below formula:
=IF(AND(F4=Sheet15!$C$1,E4>=130),MAX(G$1:G2)+1,"-")
  • Then, press the Enter key from your keyboard.

  • Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

  • Finally, we can see the result.

Formula Explanation

Here I have used different Excel functions. I have discussed every functions detail below:

MAX(number1, [number2], ...)

The MAX function takes numbers in its parameter and returns the highest value among them.

AND (logical1, [logical2], ...)

The AND function is a logical function that takes two or many logical conditions as its parameters and returns True if all the conditions are satisfied. Otherwise, it returns False.

IF (logical_condition, [value_if_true], [value_if_false])

This is another conditional function which is the IF function in Excel that takes logical conditions and returns values according to the conditions whether it is True or False.

  • Further, go to the Result2 sheet and enter the formula in cell B7.
=IFERROR(INDEX(Sheet14!F:F,MATCH(ROWS($1:1),Sheet14!$G:$G,0))&"","")
  • Hit the Enter key.

Formula Explanation

Here only the MATCH function is new that we have used so far.

MATCH(lookup_value, lookup_array, [match_type])

In the MATCH function, the first section in the parameter is used as a lookup value which is the value that we want to search or look for. The next section holds the array range of cells where we will search the lookup value and lastly match type is the -1, 0, 1 value which defines how Excel matches lookup_value with values in lookup_array.

  • Furthermore, copy down and right the array.
  • Now, select any Shop Name and it will show the Shop Name and Fruit name when the matched Shop Names fruit’s Total Price exceeds $130.

excel copy rows from one sheet to another based on criteria

Read More: Copy Rows Automatically in Excel to Another Sheet


5. Employ Excel FILTER Function to Copy Rows from One Sheet to Another

You may use the FILTER function to filter a variety of data according to the criteria that you provide. Let’s follow the procedures to use this function to copy rows from one to another based on criteria. We are going to use the following dataset. We will take the data from the FILTER sheet and copy them into the Result3 sheet.

STEPS:

  •  Firstly, we select the cell where we want to see the result.
  • And insert the formula into that cell. In our case, the cell is B5 from sheet Result3.
=FILTER(FILTER!B4:F14,FILTER!F4:F14="Rooted")
  • Further, press the Enter key from your keyboard.
  • And, That’s It! All the rows which fulfill the criteria will copy from the FILTER sheet to the Result3 sheet.

Read More: Copy and Paste Thousands of Rows in Excel


6. Using VBA for Copying Rows from One Sheet to Another 

Let’s use a VBA macro to copy the rows from VBA_Source to the VBA_Destination worksheet. The criteria for copying will be that the total prices have to be greater than $150.

STEPS:

  • The Developer tab is not visible by default. To enable it, go to the File.

Showing developer tab

  • Then choose the Options as shown below.

Excel options

  • This will open the Excel Options dialog box. Now pick Customize Ribbon. Check the Developer box and click OK.

Adding Developer tab

  • Go to the Developer tab and click Visual Basic.

  • In the VBA editor, click on Insert and choose Module.

Inserting Module

  • Insert the VBA code in the Module
Sub Copy_With_Criteria()
'Taking the Inputs
Dim Sheet1 As String
Dim Sheet2 As String
Dim Rang1 As String
Dim Range2 As String
Dim CriteriaColumn As Integer
Sheet1 = "VBA_Source"
Sheet2 = "VBA_Destination"
Range1 = "B5:E9"
Range2 = "B5"
CriteriaColumn = 4
'Forming the Necessary Ranges
Dim Rng1 As Range
Dim Rng2 As Range
Set Rng1 = Sheets(Sheet1).Range(Range1)
Set Rng2 = Sheets(Sheet2).Range(Range2)
'Copying the Headers(Optional)
Rng1.Rows(0).Copy
Rng2.Cells(0, 1).PasteSpecial Paste:=xlPasteAll
'Copying the Dataset with Criteria
Count = 0
For i = 1 To Rng1.Rows.Count
    If Rng1.Cells(i, CriteriaColumn) > 300 Then
        Count = Count + 1
        Rng1.Rows(i).Copy
        Rng2.Cells(Count, 1).PasteSpecial Paste:=xlPasteAll
    End If
Next i
Application.CutCopyMode = False
End Sub
  • Click the Run.

Running the code

  • Go back to the VBA_Destination worksheet.
  • You’ll see the rows with the total price greater than $150 have been copied.

  • To change the criteria of total price greater than $150, delete the previously copied rows in the VBA_Destination worksheet.
  • Select the range >> Cells >> Delete >> Delete Sheet Rows.

  • Edit the criteria as shown in the image below. For example, I’ve changed the criteria to greater than $300.
  • Press the Run

Changed the criteria then run the code

  • Again move to the VBA_Destination worksheet to see the results.

Result based on criteria


Download the Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above methods will assist you to copy rows from one sheet to another based on criteria in Excel. I have shown all the methods with their respective examples. If you have any other method of achieving this then please feel free to share it with us.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

1 Comment
  1. The VBA used in your example is slow and inefficient. Copy and paste and switching between sheets if not the best way of doing this. I think that teaching these methods does nothing to help develop these skills for people trying to get started and enhance their skills.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo