CODEWITHSUNDEEP
excelvbacopycopy-paste
lexi
lexi

Reputation: 17

Use VBA to copy entire row from one excel worksheet to another if match is not found in Column A

I have been running into some issues trying to use VBA to compare 2 tables in different worksheets, and then copy any rows in the "Master" sheet that are not found in the "New" sheet. Both tables are formatted as tables. The match is based on an "ID" column in Column A of both tables. If an ID is in the "Master" sheet, but not in the "New" sheet, than that entire row should be copy and pasted to the end of the table in the "New" sheet.

I updated some code found in another forum, which is almost working. However, it only seems to paste over the ID data into Column A, and not the entire corresponding row of data which is needed.

Sub compare()
Dim i As Long
Dim lrs As Long
Dim lrd As Long

With Worksheets("Master")
    lrs = .Cells(.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lrs 'assumes header in row 1
        If Application.IfError(Application.Match(.Cells(i, 1), Worksheets("New").Columns(1), 0), 0) = 0 Then
            lrd = Worksheets("New").Cells(Worksheets("test").Rows.Count, 1).End(xlUp).Row
            Worksheets("New").Cells(lrd + 1, 1).Value = .Cells(i, 1).Value
        End If
    Next i
End With
End Sub

I think the issue has to do with the "Cells" reference, instead of a range, but I do not know how to make that line dynamic.

Upvotes: 0

Views: 131

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Slightly different approach, but you need to use something like Resize() to capture the whole row, and not just the cell in Col A.

Sub compare()
    Const NUM_COLS As Long = 10 'for example
    Dim wb As Workbook, wsSrc As Worksheet, wsDest As Worksheet
    Dim c As Range, cDest As Range
    
    Set wb = ThisWorkbook 'or ActiveWorkbook for example
    Set wsSrc = wb.Worksheets("Master")
    Set wsDest = wb.Worksheets("New")
    Set cDest = wsDest.Cells(Rows.Count, 1).End(xlUp).Offset(1) 'next empty row

    For Each c In wsSrc.Range("A2:A" & wsSrc.Cells(Rows.Count, 1).End(xlUp).Row).Cells
        If IsError(Application.Match(c.Value, wsDest.Columns(1), 0)) Then
            cDest.Resize(1, NUM_COLS).Value = c.Resize(1, NUM_COLS).Value
            Set cDest = cDest.Offset(1) 'next row
        End If
    Next c
End Sub

Upvotes: 1

Related Questions