vba - copy row from one excel sheet to another based on 2 criteria inputs

Vba - copy row from one excel sheet to another based on 2 criteria inputs

To copy rows from one Excel sheet to another based on two criteria inputs using VBA, you can use a loop to iterate through the rows of the source sheet, check if the criteria match, and then copy the entire row to the destination sheet. Here's a basic example to get you started:

Sub CopyRowsBasedOnCriteria() Dim sourceSheet As Worksheet Dim destSheet As Worksheet Dim lastRow As Long Dim i As Long ' Set source and destination sheets Set sourceSheet = ThisWorkbook.Sheets("SourceSheet") Set destSheet = ThisWorkbook.Sheets("DestSheet") ' Find the last row in the source sheet lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row ' Loop through each row in the source sheet For i = 2 To lastRow ' Assuming row 1 is headers ' Check criteria (e.g., columns B and C) If sourceSheet.Cells(i, 2).Value = "Criteria1" And sourceSheet.Cells(i, 3).Value = "Criteria2" Then ' Copy entire row to destination sheet sourceSheet.Rows(i).Copy destSheet.Rows(destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1) End If Next i End Sub

In this example:

  • Change "SourceSheet" and "DestSheet" to the names of your source and destination sheets, respectively.
  • Adjust the column numbers (e.g., Cells(i, 2), Cells(i, 3)) to match the columns where your criteria are located.
  • Update the criteria ("Criteria1" and "Criteria2") to match the values you're looking for.
  • This code assumes that the data starts from row 2 and that row 1 contains headers. Adjust the For loop if your data starts from a different row.
  • The code finds the last used row in the destination sheet to paste the copied rows below it.

Run the CopyRowsBasedOnCriteria subroutine to execute the copying process. Make sure to save your workbook before running the code, as VBA macros cannot be undone.

Examples

  1. "VBA copy row to another sheet based on multiple criteria example"

    • Description: This query looks for VBA code examples demonstrating how to copy rows from one Excel sheet to another based on two criteria inputs.
    Sub CopyRowsBasedOnCriteria() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim lastRow As Long Dim i As Long ' Set the source and destination worksheets Set wsSource = ThisWorkbook.Worksheets("SourceSheet") Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet") ' Find the last row in the source sheet lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row ' Loop through each row in the source sheet For i = 2 To lastRow ' Assuming data starts from row 2 ' Check criteria and copy row if conditions are met If wsSource.Cells(i, 1).Value = "Criteria1" And wsSource.Cells(i, 2).Value = "Criteria2" Then wsSource.Rows(i).Copy wsDestination.Rows(wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1) End If Next i End Sub

  2. "Excel VBA copy row to another sheet based on two conditions"

    • Description: This query seeks VBA solutions for copying rows to another sheet in Excel based on two specified conditions.
    Sub CopyRowsWithMultipleCriteria() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim lastRow As Long Dim i As Long ' Set the source and destination worksheets Set wsSource = ThisWorkbook.Worksheets("SourceSheet") Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet") ' Find the last row in the source sheet lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row ' Loop through each row in the source sheet For i = 2 To lastRow ' Assuming data starts from row 2 ' Check criteria and copy row if conditions are met If wsSource.Cells(i, 1).Value = "Criteria1" And wsSource.Cells(i, 2).Value = "Criteria2" Then wsSource.Rows(i).Copy wsDestination.Rows(wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1) End If Next i End Sub

  3. "Excel VBA copy row based on two criteria tutorial"

    • Description: This query aims to find tutorials explaining how to use VBA to copy rows between Excel sheets based on two specified criteria.
    Sub CopyRowsBasedOnTwoCriteria() ' VBA code for copying rows based on two criteria End Sub

  4. "VBA copy rows with multiple conditions Excel"

    • Description: This query is about copying rows in Excel using VBA code with multiple conditions.
    Sub CopyRowsWithMultipleConditions() ' VBA code for copying rows with multiple conditions End Sub

  5. "Excel VBA copy row to another sheet based on two criteria example"

    • Description: This query seeks examples of VBA code to copy rows from one sheet to another in Excel based on two specified criteria.
    Sub CopyRowsToAnotherSheetBasedOnTwoCriteria() ' VBA code example for copying rows based on two criteria End Sub

  6. "VBA code to copy rows based on two conditions in Excel"

    • Description: This query looks for VBA code snippets to copy rows in Excel based on two specified conditions.
    Sub CopyRowsBasedOnTwoConditions() ' VBA code to copy rows based on two conditions End Sub

  7. "Excel VBA copy row based on 2 criteria macro"

    • Description: This query is about finding VBA macros to copy rows in Excel based on two specified criteria.
    Sub CopyRowsBasedOnTwoCriteriaMacro() ' VBA macro for copying rows based on 2 criteria End Sub

  8. "VBA copy row to another sheet based on dual criteria Excel"

    • Description: This query seeks VBA solutions to copy rows to another sheet in Excel based on two specified criteria.
    Sub CopyRowsBasedOnDualCriteria() ' VBA code to copy rows based on dual criteria End Sub

  9. "Excel VBA copy row with multiple conditions example"

    • Description: This query aims to find examples of VBA code for copying rows with multiple conditions in Excel.
    Sub CopyRowWithMultipleConditions() ' VBA code example for copying row with multiple conditions End Sub

  10. "VBA script to transfer rows based on two criteria in Excel"

    • Description: This query is about finding VBA scripts to transfer rows between sheets in Excel based on two specified criteria.
    Sub TransferRowsBasedOnTwoCriteria() ' VBA script to transfer rows based on two criteria End Sub

More Tags

header strikethrough mediacontroller angular-elements npapi datatemplate vb.net ellipsis compiled ssid

More Programming Questions

More Tax and Salary Calculators

More Physical chemistry Calculators

More Chemical thermodynamics Calculators

More Housing Building Calculators