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

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.

Download the Practice Workbook

You can download the workbook and practice with them.

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

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.

STEPS:

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

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

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

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell

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. And 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 and 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.

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

Read More: Macro to Copy and Paste from One Worksheet to Another (15 Methods)

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.

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 down and right the formula to get the total 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.

• Go to the Rooted worksheet and see the changes.

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria

Similar Readings

• How to Copy Alternate Rows in Excel (4 Ways)
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• How to Copy Rows in Excel with Filter (6 Fast Methods)
• Excel VBA to Copy Data from Another Workbook without Opening
• How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)

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 Functions sheet and enter the below formula:

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

• 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 is greater than $130.

Read More: Excel VBA: Copy Range to Another Workbook

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.

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

6. Apply Excel VBA for Copying Rows from One Sheet to Another 

Here I will do the same thing as the previous example, but by utilizing VBA. We will find out the list of fruits whose total prices are greater than 150 from sheet VBA to sheet Result4  by clicking on a command button. Let’s see the steps to copy rows from one sheet to another based on criteria in Excel.

STEPS:

• In the first place, go to the Developer Tab.
• Then, select the ActiveX Control under the Insert option.

• Change the button caption and font using the Properties option.

• Click on the button it will move you to the VBA window. Write the code like this:

Code:

Private Sub CommandButton1_Click()
a = Worksheets("VBA").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("VBA").Cells(i, 4).Value > 150 Then
Worksheets("VBA").Rows(i).Copy
Worksheets("Result4").Activate
b = Worksheets("Result4").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Result4").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("VBA").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("VBA").Cells(1, 1).Select
End Sub

• After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5. Or, click on the button.

VBA Code Explanation

Here the important lines of the code are explained.

• We are counting the total row number from VBA and storing it in variable a.
• Using IF conditions we are checking the Total Price of each fruit row.
• Again, count the row number of Result4 and store it in variable b.
• Selecting the matched values by incrementing the value of b.

• After that, we click on the Run button of VBA.
• Finally, go to sheet Result4 then you will see copied rows from one sheet to another based on criteria in Excel.

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

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

• How to Display Text from Another Cell in Excel (4 Suitable Ways)
• VBA Code to Compare Two Excel Sheets and Copy Differences
• How to Use VBA to Paste Values Only with No Formatting in Excel
• Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
• How to Copy Multiple Rows in Excel Using Macro (4 Examples)