Excel Copy Rows from One Sheet to Another Based on Criteria (5 Ways)

Copy Rows From a Sheet to Another Based on Column Criteria (Filtering and Copying)

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.

 Copy Rows From a Sheet to Another Based on Column Criteria (Filtering and Copying)

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

Select the Filter option at 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.

Select your desired column which you will copy 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.

 Then select the row which you want to copy from this Worksheet. I have selected the Mango row here for the example

Step 4: Copy the entire data.

 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.

Paste the copied data into 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.

Go to Sheet4 and select Advanced under the Data section

Step 2: Select Copy to another location option.

Select Copy to another Location option.

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

Select the List range box and go to Sheet3 and copy the full dataset

Step 4: Then select the Criteria range cell.

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.

 Now select 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.

Final result after copying using advanced option.

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.

Copy Rows To Another Worksheet Based on Text Using Formula (Using VBA)

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

Go to properties

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

 VBA code

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:

VBA code explanation

Here the important lines of the code are explained. 

  1. We are counting the total row number from Sheet5 and storing it in variable a.
  2. Using IF conditions we are checking the Total Price of each fruit row.
  3. Again, counting the row number of Sheet6 and storing it in variable b.
  4. 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.

After clicking

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.

How to Copy Rows from One Sheet to Another Using Array Formula

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.

Copy Rows From One Sheet to Another Using Array Formula

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

Go to any new worksheet like Rooted. Then select the cell B4 and enter the below formula then press CTRL + SHIFT + ENTER

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

Copy down and right the formula to get the total matched rows.

Step 4: Do the same way for other worksheets.

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.

Change price of any fruits

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

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.  

How to Copy Rows and Automatically Stored in another Worksheet

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.

Select the G4 cell in Sheet14 and enter the below formula

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.

Go to Sheet15 and Enter the formula in cell A4

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.

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

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

ExcelDemy
Logo