Excel VBA: Copy Row If Cell Value Matches (2 Methods)

While differentiating data, we need to extract the specific data. So, we have to extract them all based on particular values. We have to choose the VBA over Excel basic features to do the task. We will show you how to copy a row if the cell value matches in Excel VBA.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Suitable Methods to Copy Row If Cell Value Matches Using VBA in Excel

In the sections below, we will discuss two examples of copying rows based on matching cells value. Firstly, we will copy rows for one matched value and show them on a different sheet. Later on, we will apply the cell value match for multiple criteria and represent them in multiple sheets. In the below image, a sample data set is shown from where we will extract information.

1. Copy Row to Another Sheet If One Cell Value Matches Using VBA in Excel

For Example, from the data set in ‘Dataset1’, we want to differentiate the sold item. Besides, we want to show them in worksheet ‘Sold1’. To do the task, follow the simple steps below.

Step 1: Create a Module

• First of all, press Alt + F11 to start the VBA Macro.
• Click on the Insert.
• Select the Module option.

Step 2: Paste the VBA Codes

• Paste the following VBA codes.

Sub CopyRow()
'Declare variables
    Dim DataRg As Range
    Dim DataCell As Range
    Dim P As Long
    Dim J As Long
    Dim I As Long
'Set variables
    P = Worksheets("Dataset1").UsedRange.Rows.Count
    Q = Worksheets("Sold1").UsedRange.Rows.Count
''Type If condition to relate the variables I and Q
    If I = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Sold1").UsedRange) = 0 Then Q = 0
    End If
'Set range for Dataset1
    Set DataRg = Worksheets("Dataset1").Range("E5:E11" & P)
    On Error Resume Next
    Application.ScreenUpdating = False
'Apply the For loop
    For I = 1 To DataRg.Count
'Set Condition for "Sold" value
        If CStr(DataRg(I).Value) = "Sold" Then
'Apply command to copy cells
            DataRg(I).EntireRow.Copy Destination:=Worksheets("Sold1").Range("A" & Q + 1)
            Q = Q + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Step 3: Run the Program

• Save the program and press F5 to run it.

Read More: Copy Values to Another Cell If Two Cells Match in Excel: 3 Methods

Similar Readings:

• Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
• How to Find Case Sensitive Match in Excel ( 6 Formulas)
• How to Match Names in Excel Where Spelling Differ (8 Methods)

2. Copy Row to Multiple Sheets for Multiple Criteria at Once If Cell Value Matches Using VBA in Excel

It’s a very important section, as you can learn to copy and move cells based on the matched values in multiple sheets simultaneously. Let’s say, we have a dataset in worksheet ‘Dataset2’ and we want to place the sold products in the worksheet ‘Sold2’ and the unsold products in the worksheet ‘Unsold2’. Follow the outlined procedure to accomplish the task.

Step 1: Select a New Module

• Insert a new Module.

Step 2: Paste the VBA code

• Copy and paste the following VBA code into the Module.

Sub CopyRow2()
'Declare variables
    Dim sheetNo1 As Worksheet
    Dim sheetNo2 As Worksheet
    Dim sheetNo3 As Worksheet
    Dim FinalRow As Long
    Dim Cell As Range
'Set variables
    Set sheetNo1 = Sheets("Dataset2")
    Set sheetNo2 = Sheets("Sold2")
    Set sheetNo3 = Sheets("Unsold2")
'Type a command to select the entire row
    Selection.EntireRow.Select
' Define destination sheets to move row
    FinalRow1 = sheetNo1.Range("A" & sheetNo1.Rows.Count).End(xlUp).Row
    FinalRow2 = sheetNo2.Range("A" & sheetNo2.Rows.Count).End(xlUp).Row
    FinalRow3 = sheetNo3.Range("A" & sheetNo3.Rows.Count).End(xlUp).Row
    With sheetNo1
'Apply loop for column E until last cell with value
    For Each Cell In .Range("E1:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
'Apply condition to match the "Sold" value
        If Cell.Value = "Sold" Then
'Command to Copy and move to a destination Sheet "Sold2"
            .Rows(Cell.Row).Copy Destination:=sheetNo2.Rows(FinalRow2 + 1)
            FinalRow2 = FinalRow2 + 1
'Apply condition to match the "Unsold" value
        ElseIf Cell.Value = "Unsold" Then
'Command to Copy and move to a destination Sheet "Unsold2"
            .Rows(Cell.Row).Copy Destination:=sheetNo3.Rows(FinalRow3 + 1)
             FinalRow3 = FinalRow3 + 1
        End If
     Next Cell
    End With
End Sub

Step 3: Run the Program

• Save the program and press F5 to run it.
• Therefore, you will see that the sold products are placed in the worksheet ‘Sold2’.

• Moreover, the unsold products are shown in the worksheet ‘Unsold2’.

Notes.  If you want to entirely move the rows from the source data set, follow the steps below.

Step 1: Add the Following VBA Codes

• Add the VBA codes marked in the image below with the previous one.
• Therefore, your final codes become:

Sub CopyRow4()
'Declare variables
    Dim sheetNo1 As Worksheet
    Dim sheetNo2 As Worksheet
    Dim sheetNo3 As Worksheet
    Dim FinalRow As Long
    Dim Cell As Range
'Set variables
    Set sheetNo1 = Sheets("Dataset2")
    Set sheetNo2 = Sheets("Sold2")
    Set sheetNo3 = Sheets("Unsold2")
'Type a command to select the entire row
    Selection.EntireRow.Select
' Define destination sheets to move row
    FinalRow1 = sheetNo1.Range("A" & sheetNo1.Rows.Count).End(xlUp).Row
    FinalRow2 = sheetNo2.Range("A" & sheetNo2.Rows.Count).End(xlUp).Row
    FinalRow3 = sheetNo3.Range("A" & sheetNo3.Rows.Count).End(xlUp).Row
    With sheetNo1
'Apply loop for column E until last cell with value
    For Each Cell In .Range("E1:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
'Apply condition to match the "Sold" value
        If Cell.Value = "Sold" Then
'Command to Copy and move to a destination Sheet "Sold2"
            .Rows(Cell.Row).Copy Destination:=sheetNo2.Rows(FinalRow2 + 1)            
            .Rows(Cell.Row).Delete            
            FinalRow2 = FinalRow2 + 1
'Apply condition to match the "Unsold" value
        ElseIf Cell.Value = "Unsold" Then
'Command to Copy and move to a destination Sheet "Unsold2"
            .Rows(Cell.Row).Copy Destination:=sheetNo3.Rows(FinalRow3 + 1)            
            .Rows(Cell.Row).Delete          
             FinalRow3 = FinalRow3 + 1
        End If
     Next Cell
    End With
  End Sub

Step 2: Run the Codes

• Click on the Save icon and press F5 to run the program.
• As a result, your source dataset will be removed as the figure shown below.

• Just Like before, you will get the sold values in the ‘Sold2’.

• Moreover, unsold values are in the ‘Unsold2’.

Conclusion

Finally, I hope you now understand how to copy a row if the cell value matches in Excel VBA. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.

The Exceldemy staff will get back to you as soon as possible.

Stay with us and continue to learn.

Related Articles

• Excel VBA to Match String in Column (5 Examples)
• How to Return Row Number of a Cell Match in Excel (7 Methods)
• Excel Find Matching Values in Two Columns
• How to Vlookup and Pull the Last Match in Excel (4 Ways)