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.

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

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: Excel VBA: Copy Cell Value and Paste to Another Cell

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.

Read More: How to Copy Multiple Rows in Excel Using Macro

Download Practice Workbook

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

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 comment section below.

Stay with us and continue to learn.

Related Articles

• How to Paste From Clipboard to Excel Using VBA
• How to Copy Visible Cells Only without Header Using VBA
• How to Use VBA PasteSpecial for Formulas and Formats in Excel
• Excel VBA to Copy Only Values to Destination
• How to Use VBA PasteSpecial to Keep Source Formatting in Excel
• How to Use VBA to Paste Values Only with No Formatting in Excel
• Copy and Paste Values to Next Empty Row with Excel VBA
• How to Autofilter and Copy Visible Rows with Excel VBA
• VBA Paste Special to Copy Values and Formats in Excel