In MS-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 do that in Excel.

## Download the Practice Workbook

**5 Ways to copy Rows from One Sheet to another Based on Criteria in Excel**

**1. Copy Rows from a Sheet to Another Based on Column Criteria (Filtering and Copying)**

For demonstrating this process letâ€™s consider a dataset of some fruits with their **unit price**, **weight**, **total price**. This full table is saved on **Sheet1** in our workbook. Now we will copy the rows from **Sheet1 **and will save them on S**heet2** using **Filtering** and **Copying **options.

**Step 1:** Select the **Filter** option in the **Data **section.

**Step 2:** Select your desired column to which you are going to copy the rows. Here I have selected the **Fruits** column.

**Step 3:** 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.

**Step 4:** Copy the entire data.

**Step 5:** Create a new worksheet by clicking on the **+** (plus) sign below or you can use the keyboard shortcut **SHIFT + F11.**

**Step 6:** Paste the copied data into the new worksheet **Sheet2**. Then all the selected data will be copied from **Sheet1** to **Sheet2**.

**2.How to Copy Rows from One Worksheet to another Based on Criteria Using Advanced Option in Excel**

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.

**Step 1:** Go to **Sheet4 **and select **Advanced **under the **Data** section.

**Step 2:** Select **Copy to another location** option.

**Step 3:** Select the **List range** box and go to **Sheet3** and copy the full dataset.

**Step 4:** Then select the **Criteria range** cell.

**Step 5:** Now select the **Copy to** option which will shift automatically to **Sheet4 **and select any cell of that worksheet. Then press the **Ok** button.

Then rows will be copied from **Sheet3 **to **Sheet4 **based on mentioned criteria.

**3. How to Copy Rows To Another Worksheet Based on Text Using Formula (Using VBA).**

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 **Sheet5** to **Sheet6** by clicking on a command button.

**Step 1:** First go to the **Developer** Tab.

**Step 2:** Select the **ActiveX Control** under the **Insert **option.

**Step 3:** Change the button caption and font using the **Properties** option.

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

#### Code:

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

**Explanation:**

Here the important lines of the code are explained.Â

- We are counting the total row number from
**Sheet5**and storing it in variable a. - Using IF conditions we are checking the
**Total Price**of each fruit row. - Again, counting the row number of
**Sheet6**and storing it in variable b. - Selecting the matched values by incrementing the value of b.

*This idea of the code is collected from this Youtube channel:*

*Â*

**Step 6:** After that, we click the button on **Sheet5** and then go to **Sheet6.**

### 4. How to 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.

**Step 1:** Create new worksheets with the **Shop Names**.

**Step 2:** Go to any new worksheet like **Rooted. **Then select cell **B4 **and enter the below formula then press **CTRL + SHIFT + 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)**

This **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])**

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 this Excel function we are able to determine the **n**th 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 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.

**Step 3:** Copy down and right the formula to get the total matched rows.

**Step 4:** Do the same way for other worksheets.

**Step 6:** You can check by changing any value in **Sheet7** whether the value is automatically changing or not.

**Step 7:** Go to the Rooted worksheet and see the changes.

**5. How to Copy Rows and Automatically Stored in another Worksheet**

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 **Sheet14** and shown in **Sheet15**. **Â **

**Step 1:** Select the **G4** cell in **Sheet14** and enter the below formula:

`=IF(AND(F4=Sheet15!$C$1,E4>=130),MAX(G$1:G2)+1,"-")`

**Formula Explanation**

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

**MAX(number1, [number2], â€¦)**

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

**AND (logical1, [logical2], â€¦)**

This 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 in Excel that takes logical conditions and returns values according to the conditions whether it is **True** or **False**.

**Step 2:** Go to **Sheet15** and Enter the formula in cell **A4.**

`=IFERROR(INDEX(Sheet14!F:F,MATCH(ROWS($1:1),Sheet14!$G:$G,0))&"","")`

**Formula Explanation**

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

**MATCH(lookup_value, lookup_array, [match_type])**

In this 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.

**Step 3:** Copy down and right the array.

**Step 4:** 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**.

**Conclusion**

These are the ways of copying rows from one sheet to another based on different criteria. 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.

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.