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 Sheet2 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:
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
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.
In this formula, we have used some Excel functions. Here I will discuss all the functions which were used in the array formula.
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.
Bypassing the cell reference in the parameter of the COLUMN function we will get the specific column number according to our given cell reference.
Using this Excel 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.
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:
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.
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.
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.