Copy Rows To Another WorkSheet Based On Criteria [Excel VBA]

Connecting terms: excel macro copy row to another sheet, macro to pull data from another worksheet based on criteria, vba copy row to another sheet, excel vba copy row to another sheet, how to copy and paste multiple rows in excel

Let’s assume you have a workbook called Invoices that contains two worksheets: Sheet 1= Raw Data and Sheet 2= Filtered Data. The Raw Data sheet contains a list of invoices, including the invoice number, date, customer name, and amount. On the contrary, the Worksheet 2 named as the Filtered Data contains statements that meet a specific criteria, such as amount greater than $1000.

Now, you want to copy the rows of the second sheet to the first one. That’s where VBA can automate the task for you.

In this guide, I will show you how to copy one or multiple rows in Excel to another worksheet with Visual Basic Codes.

So, let’s get started.

How To Copy Rows to Another Worksheet Based on Text & Number Criteria

There are two ways to use macro to pull data from another worksheet. One is based on Text Criteria and the other is based on Number Criteria. Both use VBA Codes. So, if you are a beginner to Visual Basic Codes, you should first know how to enable vba in Excel as by default, this option is disabled.

So, here are the ways to use vba to copy a row to another sheet:

Method 1: Copy Row Based on Text Criteria

A text criteria are a specific value or pattern used to filter data based on the contents of a text-based cell. Consider the data set in the image below.

Sheet 1 is the Raw Data Sheet. I have the invoice number, date, customer name, and amount in that worksheet 1. I want to extract and copy the data of Aurora Roy in the Filtered Data Sheet.

Follow the steps below:

1. Go to Developer Tab and Click on the Visual Basic Option.
2. Click on Insert Module which will open up a dialog box.
3. Insert the following code in the box.

   Sub CopyRows()
   Dim ws1 As Worksheet, ws2 As Worksheet
   Set ws1 = ThisWorkbook.Sheets("Raw Data") 'set first worksheet
   Set ws2 = ThisWorkbook.Sheets("Filtered Data") 'set second worksheet
   lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row 'find last row in the first worksheet
   For i = 2 To lastRow 'loop through rows in the first worksheet, starting from row 2 (to skip the header row)
   If ws1.Cells(i, "C").Value = "Aurora Roy" Then 'check if the value in column C (Customer name) is equal to "Aurora Roy"
   ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Value = ws1.Range("A" & i).EntireRow.Value 'copy the entire row to the second worksheet
   End If
   Next i
   End Sub

4. Run the Code using F5 or Click Sub/UserForm.
5. Go to the Excel Worksheet by clicking on the Excel icon.

Note: Change the variables and range of cells as per your requirements.

Explanation of the Code

The VBA Code mentioned here starts with a subroutine called CopyRows. You can change the name as you like. The Next line is declaring two variables, ws1 and ws2, as objects of the Worksheet type.

Now, I am setting ws1 and ws2 as the worksheet name currently available in the workbook, which is referred to as ThisWorkbook.

After that, I am commanding Excel to count the number of rows in the worksheet using the LastRow function and using the End(xlUp) method to find the last non-empty row. Then, by adding a For Loop, I am commanding to iterate from Row 2 to LastRow. The loop variable i is used to represent the current row in the first worksheet. The loop starts from row 2 because it’s assumed that row 1 is a header row and should be skipped.

Next, I am adding a condition using the IF Function to check if the value in the cell in row i and column C of the first worksheet is equal to the text Aurora Roy. If it is, then the next section of code inside the If statement will be executed.

The Line ws2.Range copies the entire row from the first worksheet (Raw Data) to the second worksheet (Filtered Data) if the value in column C is equal to Aurora Roy. The row in the second worksheet is copied to the next empty row by using the End(xlUp) method to find the last non-empty row in column A and then using the Offset method to move down one row. The value of the entire row in the first worksheet is assigned to the entire row in the second worksheet using the Value property.

Finally, close all the statements of IF, Loop and Subroutine to cover up the code.

Note: If by any chance you are seeing that the amount in the invoice is in text format instead of being in the default number format, you have to use another vba code to convert text format to number format.

Method 2: Duplicate Rows Based On Number Criteria

If you want to copy rows to another worksheet based on cell value (amount / number) then try this macro code instead.

Suppose I want to extract only the amount from the Raw Data that is greater than or equal to 1000.

The Macro Code I will be inserting here is:

Sub CopyRowsBasedOnCriteria()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Set sourceSheet = ThisWorkbook.Sheets("Raw Data") 'Change "Raw Data" to your source sheet name
Set targetSheet = ThisWorkbook.Sheets("Filtered Data") 'Change "Filtered Data" to your target sheet name
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow 'assuming the header row is in row 1
    If sourceSheet.Cells(i, 4).Value >= 1000 Then 'change the criteria as needed (in this case, "Amount" >= 1000)
        sourceSheet.Rows(i).Copy targetSheet.Rows(targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1)
    End If
Next i
End Sub

Here, I am assigning this code that the source worksheet (Raw Data) contains all the necessary data, and the target worksheet (Filtered Data) is where I want to paste the rows. I am setting a  condition in this case that the Amount in column D must be greater than or equal to 1000. If so, then paste the rows in the dedicated worksheet. You can change this criterion as required by modifying the If statement.

Final Words

The code mentioned here is based on my dataset. If you have a different one, then ensure that you modified the variables. If you want to filter data based on Number format, then follow the second method. Else, if you want to extract data based on cell text, then go for the first method.