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.

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

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.

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

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

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

Step 3: Run the Program

  • Save the program and press F5 to run it.

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

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


Similar Readings:


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.

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

Step 1: Select a New Module

  • Insert a new Module.

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

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

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

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’.

Suitable Methods to Copy Row If Cell Value Matches with Excel VBA

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

Sample Data

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

Sample Data

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.

Sample Data

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

Sample Data

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

Sample Data


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

Tags:

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  1. Hello,

    I’m trying to apply this to many Sheets will many Different Cell types.
    Would you be able to help with this?
    This is what I have so far, but I’m getting a “subscript out of range” error.

    Sub MoveRowBasedOnCellValue()
    ‘Declare Variables
    Dim MAIN As Worksheet
    Dim Project As Worksheet
    Dim Claims As Worksheet
    Dim TaM As Worksheet
    Dim QUOTED As Worksheet
    Dim PM As Worksheet
    Dim FinalRow As Long
    Dim Cell As Range

    ‘Set Variables
    Set MAIN = Sheets(“Dataset2”)
    Set Claims = Sheets(“Claims”)
    Set Project = Sheets(“Project”)
    Set TaM = Sheets(“TaM”)
    Set PM = Sheets(“PM”)
    Set QUOTED = Sheets(“QUOTED”)

    ‘Type a Command to select the entire row
    Selection.EntireRow.Select

    ‘Define destination sheets to move row
    FinalRow1 = MAIN.Range(“A” & MAIN.Rows.Count).End(xlUp).Row
    FinalRow2 = Claims.Range(“A” & Claims.Rows.Count).End(xlUp).Row
    FinalRow3 = Project.Range(“A” & Project.Rows.Count).End(xlUp).Row
    FinalRow4 = TaM.Range(“A” & TaM.Rows.Count).End(xlUp).Row
    FinalRow5 = QUOTED.Range(“A” & QUOTED.Rows.Count).End(xlUp).Row
    FinalRow6 = PM.Range(“A” & PM.Rows.Count).End(xlUp).Row
    With MAIN

    ‘Apply loop for column F until last cell with value
    For Each Cell In .Range(“F1:F” & .Cells(.Rows.Count, “F”).End(xlUp).Row)

    ‘Apply condition to match the “CLAIMS” value
    If Cell.Value = “CLAIMS” Then
    ‘Command to Copy and move to a destination Sheet “CLAIMS”
    .Rows(Cell.Row).Copy Destination:=Claims.Rows(FinalRow2 + 1)
    FinalRow2 = FinalRow2 + 1
    ‘Apply condition to match the “PROJECT” value
    ElseIf Cell.Value = “EQUIPMENT PROJECT” Then
    ‘Command to Copy and move to a destination Sheet “PROJECT”
    .Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
    FinalRow3 = FinalRow3 + 1
    ‘Apply condition to match the “PROJECT” value
    ElseIf Cell.Value = “CONTRACTING PROJECT” Then
    ‘Command to Copy and move to a destination Sheet “PROJECT”
    .Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
    FinalRow3 = FinalRow3 + 1
    ‘Apply condition to match the “TaM” value
    ElseIf Cell.Value = “T&M” Then
    ‘Command to Copy and move to a destination Sheet “TaM”
    .Rows(Cell.Row).Copy Destination:=TaM.Rows(FinalRow4 + 1)
    FinalRow4 = FinalRow4 + 1
    ‘Apply condition to match the “Quoted” value
    ElseIf Cell.Value = “QUOTED” Then
    ‘Command to Copy and move to a destination Sheet “PROJECT”
    .Rows(Cell.Row).Copy Destination:=QUOTED.Rows(FinalRow5 + 1)
    FinalRow5 = FinalRow5 + 1
    ‘Apply condition to match the “SVC AGR” value
    ElseIf Cell.Value = “SVC AGR” Then
    ‘Command to Copy and move to a destination Sheet “PM”
    .Rows(Cell.Row).Copy Destination:=PM.Rows(FinalRow6 + 1)
    FinalRow6 = FinalRow6 + 1
    End If
    Next Cell
    End With
    End Sub

    Any help would be appreciated.

    Thank you,
    Andrea

    • First of all, take cordial greetings from the ExcelDemy team. There could be some possible reasons due to the issue:
      The inverted commas are not properly inserted in your code as they should be in VBA script.
      The Sheet Names might not be inserted in VBA exactly as they are in the Excel file.
      However, we have created an Excel file precisely in accordance with your requirements. We can send the Excel file if you provide your email address.
      Otherwise, you can copy the codes below that we have used to make the Excel file with the right syntax. Please continue to provide us with feedback; ExcelDemy always welcomes your concerns.

      Sub MoveRowBasedOnCellValue()
      ‘Declare Variables
      Dim MAIN As Worksheet
      Dim Project As Worksheet
      Dim Claims As Worksheet
      Dim TaM As Worksheet
      Dim QUOTED As Worksheet
      Dim PM As Worksheet
      Dim FinalRow As Long
      Dim Cell As Range

      ‘Set Variables
      Set MAIN = Sheets(“Dataset2”)
      Set Claims = Sheets(“CLAIMS”)
      Set Project = Sheets(“Project”)
      Set TaM = Sheets(“TaM”)
      Set PM = Sheets(“PM”)
      Set QUOTED = Sheets(“QUOTED”)

      ‘Type a Command to select the entire row
      Selection.EntireRow.Select

      ‘Define destination sheets to move row
      FinalRow1 = MAIN.Range(“A” & MAIN.Rows.Count).End(xlUp).Row
      FinalRow2 = Claims.Range(“A” & Claims.Rows.Count).End(xlUp).Row
      FinalRow3 = Project.Range(“A” & Project.Rows.Count).End(xlUp).Row
      FinalRow4 = TaM.Range(“A” & TaM.Rows.Count).End(xlUp).Row
      FinalRow5 = QUOTED.Range(“A” & QUOTED.Rows.Count).End(xlUp).Row
      FinalRow6 = PM.Range(“A” & PM.Rows.Count).End(xlUp).Row
      With MAIN

      ‘Apply loop for column F until last cell with value
      For Each Cell In .Range(“F1:F” & .Cells(.Rows.Count, “F”).End(xlUp).Row)

      ‘Apply condition to match the “CLAIMS” value
      If Cell.Value = “CLAIMS” Then

      ‘Command to Copy and move to a destination Sheet “CLAIMS”
      .Rows(Cell.Row).Copy Destination:=Claims.Rows(FinalRow2 + 1)
      FinalRow2 = FinalRow2 + 1

      ‘Apply condition to match the “PROJECT” value
      ElseIf Cell.Value = “EQUIPMENT PROJECT” Then

      ‘Command to Copy and move to a destination Sheet “PROJECT”
      .Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
      FinalRow3 = FinalRow3 + 1

      ‘Apply condition to match the “PROJECT” value
      ElseIf Cell.Value = “CONTRACTING PROJECT” Then

      ‘Command to Copy and move to a destination Sheet “PROJECT”
      .Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
      FinalRow3 = FinalRow3 + 1

      ‘Apply condition to match the “TaM” value
      ElseIf Cell.Value = “T&M” Then

      ‘Command to Copy and move to a destination Sheet “TaM”
      .Rows(Cell.Row).Copy Destination:=TaM.Rows(FinalRow4 + 1)
      FinalRow4 = FinalRow4 + 1

      ‘Apply condition to match the “Quoted” value
      ElseIf Cell.Value = “QUOTED” Then

      ‘Command to Copy and move to a destination Sheet “QUOTED”
      .Rows(Cell.Row).Copy Destination:=QUOTED.Rows(FinalRow5 + 1)
      FinalRow5 = FinalRow5 + 1

      ‘Apply condition to match the “SVC AGR” value
      ElseIf Cell.Value = “SVC AGR” Then

      ‘Command to Copy and move to a destination Sheet “PM”
      .Rows(Cell.Row).Copy Destination:=PM.Rows(FinalRow6 + 1)
      FinalRow6 = FinalRow6 + 1
      End If

      Next Cell

      End With

      End Sub

Leave a reply

ExcelDemy
Logo