How to Copy a Row Using Excel VBA If the Cell Value Matches (2 Methods)

Dataset Overview

We’ll use the sample dataset below to demonstrate two methods.

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

From the data set in Dataset1, we want to differentiate the sold items and show them in worksheet Sold1.

Step 1 – Create a Module

• Press Alt + F11 to start the VBA Macro.
• Click on the Insert.
• Select the Module option.

Step 2 – Paste the VBA Codes

• Enter the following VBA code:

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

Method 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. 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.
• As a result, the sold products will be placed in the worksheet Sold2, and the unsold products will appear in the worksheet Unsold2.

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

Step 1 – Add the Following VBA Codes

• Insert the VBA code shown in the image below along with the existing code. Your final code should look like this:

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 shows below.

• The Sold values will be in the Sold2 sheet.

• The unsold values will be in the Unsold2 sheet.

Download Practice Workbook

You can download the practice workbook from here:

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