Excel VBA to Copy Rows to Another Worksheet Based on Criteria

Coping data, based on criteria, from one sheet to another is one of the frequently done tasks in Excel. If you have a lot of rows and want to copy them on another sheet based on some criteria it can be quite tiresome and time-consuming. But with Microsoft Visual Basic Application (VBA) you can create Macros with which you can easily copy data from one sheet to another based on different criteria. In this article, I’ll show you how you can copy rows to another worksheet based on 2 different types of criteria by using Excel VBA.

Let’s say, you have the following dataset in a worksheet named “Data” where sales and sales areas of different salesman are given. Now, you want to copy specific rows based on some criteria in another sheet.

dataset


Download Practice Workbook


2 Ways to Copy Rows to Another Worksheet Based on Criteria by Excel VBA

1. Copy Rows to Another Worksheet Based on Text Criteria

In this demonstration, I’ll show you how you copy rows from one worksheet to another based on text criteria. Suppose, you want to copy the data of the salesmen who sell in Virginia in a worksheet named Area Sales using Excel VBA. To do that, first,

➤ Press ALT+F11 to open the VBA window.

In the VBA window,

➤ Click on the Insert tab and select Module.

module

It will open the Module(Code) window. Now,

➤ Insert the following code in the Module(Code) window,

Sub Copy_Criteria_Text()

Application.ScreenUpdating = False
With ActiveSheet
    .AutoFilterMode = False
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Virginia"
        On Error Resume Next
        .Offset(1).EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
    .AutoFilterMode = False
End With
Application.ScreenUpdating = True
Sheet3.Select

End Sub

The code will create a Macro named  Copy_Criteria_Text  that will search for Virginia in column C of the current worksheet and return the rows which contain Virginia in the worksheet named Area Sales (Sheet3).

excel vba copy rows to another worksheet based on criteria

After that,

➤ Close or minimize the VBA window.

➤ Press ALT+F8

It will open the Macro window.

➤ Select Copy_Criteria_Text in the Macro name box and click on Run.

excel vba copy rows to another worksheet based on criteria

As a result, the rows with Virginia will be copied in the worksheet named Area Sales

excel vba copy rows to another worksheet based on criteria

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


2. Copy Rows to Another Worksheet Based on Number Criteria

Now, I’ll show you how you can copy rows from one worksheet to another worksheet based on number criteria. Suppose, you want to copy the data of the sales which is greater than $100000 to a worksheet named Top Sales. To do that, first,

➤ Press ALT+F11 to open the VBA window.

In the VBA window,

➤ Click on the Insert tab and select Module.

vba window

It will open the Module(Code) window. Now,

➤ Insert the following code in the Module(Code) window,

Sub Copy_Criteria_Number()

Application.ScreenUpdating = False
With ActiveSheet
    .AutoFilterMode = False
    With Range("D1", Range("D" & Rows.Count).End(xlUp))
        .AutoFilter 1, ">100000"
        On Error Resume Next
        .Offset(1).EntireRow.Copy Sheet4.Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
    .AutoFilterMode = False
End With
Application.ScreenUpdating = True
Sheet4.Select

End Sub

The code will create a Macro named  Copy_Criteria_Number  that will search for values greater than 100000 in column D of the worksheet named Data and return the rows which contain sales values more than $100000  in the worksheet named Top Sales (Sheet4).

excel vba copy rows to another worksheet based on criteria

After that,

➤ Close or minimize the VBA window.

➤ Press ALT+F8

It will open the Macro window.

➤ Select Copy_Criteria_Number in the Macro name box and click on Run.

excel vba copy rows to another worksheet based on criteria

As a result, the rows which contain sales values of more than $100000 will be copied into the Top Sales worksheet.

excel vba copy rows to another worksheet based on criteria

Read More: How to Copy Multiple Rows in Excel Using Macro (4 Examples)


Conclusion

With the two methods of this article, you’ll be able to copy rows from one worksheet to another worksheet based on different criteria by using Excel VBA. You can use the first method for text criteria and the second method for number criteria. If you have any type of confusion about any of the methods please feel free to leave a comment.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

2 Comments
  1. I understand the demonstration. However, my question is to take the information from the master sheet in a workbook and place it on another sheet with the same catagory within the same workbook. How can that be done?

Leave a reply

ExcelDemy
Logo