Excel - Copy rows based on a condition

December 2016

Microsoft Office Software comes with the Excel application, which has become a spreadsheet application used worldwide thanks to its user-friendly data sorting system. The Excel Office Software application allows you to copy data contained in one row to another row and copy the same data from one row to multiple rows using the Fill property. It saves you from having to retype the information. It is also possible to copy rows from one worksheet, which has headings on row 1 and data starting from row 2 to another worksheet where the column contains terms. You are required to write a simple code to copy rows from your sheet to another worksheet.

Issue



How can I have Excel copy the entire row of data from a worksheet called Employee Inventory to another worksheet called EEs, if column Q contains TERM. I've tried almost everything but nothing is working. Please help!

Solution


Assumptions for this code:
1. The Employee Inventory sheet has headings in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. The EEs sheet will have headings in row one so the data will start copying in row 2.

Set i = Sheets("Employee Inventory")   
Set e = Sheets("EEs")   
Dim d   
Dim j   
d = 1   
j = 2   

Do Until IsEmpty(i.Range("Q" & j))   

    If i.Range("Q" & j) = "TERM" Then   
    d = d + 1   
    e.Rows(d).Value = i.Rows(j).Value   
       
    End If   
    j = j + 1   
Loop

Note


Thanks to Helper for this tip on the forum.

Related :

This document entitled « Excel - Copy rows based on a condition » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.