Copy Data from one worksheet to the next - only add row if the unique value is NOT there - otherwise just update

Hi.

This code should work (within your marco). It assumes you deliver a String variable called Search (or change it in the code below) and that your IDs are in column A (starting at A2). You need to add your code at the points identified by comments below:

Dim IDs As Range, LastRow As Integer

'  assumes you deliver a String variable called Search 
With Worksheets("Destination")

    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    Set IDs = .Range("A2:A" & LastRow)

    For Each Cell In IDs
        If Cell.Value = Search Then
        Debug.Print "Found"
            'Your code to update row (= Cell.Row)
            Exit Sub
        End If
    Next Cell
' Your code to add new row (= LastRow+1)

End With

REVISION 1: I think I've worked out what you want to do. In the attached revision to your file, I've added this code in Module 1 REVISION 2: Corrected second IF statement (for flag=False) and moved Updated=False,  dest_last_row= and Set IDs= statements within the For/Next loop (so multiple "Not found" items are added to Destination, changes in bold italics below):

Sub AddNewOrUpdate()

'Defining Worksheets & Other
    Dim Impsheet As Worksheet
    Dim Destsheet As Worksheet
    Dim Search As Variant
    Dim IDs As Range, Updated As Boolean
    Dim dest_last_row As Integer, Import_last_row As Integer

    Set Impsheet = Sheets("SFEXport")
    Set Destsheet = Sheets("Destination")

    'Get the last row of the import worksheet
    Import_last_row = Sheets("SFExport").Cells(Rows.Count, 1).End(xlUp).Row
    'Get last row for destination sheet
    dest_last_row = Destsheet.Cells(Rows.Count, 1).End(xlUp).Row

    Set IDs = Destsheet.Range("A2:A" & dest_last_row)

    

'Loop through Impsheet
For J = Import_last_row To 2 Step -1

Search = Cells(J, 1).Value
Updated = False
dest_last_row = Destsheet.Cells(Rows.Count, 1).End(xlUp).Row
Set IDs = Destsheet.Range("A2:A" & dest_last_row)

    'Output
    For Each Cell In IDs
        If Cell.Value = Search Then
            'Update the cells that might change
            Destsheet.Cells(Cell.Row, 4) = Impsheet.Cells(J, 4)
            Destsheet.Cells(Cell.Row, 5) = Impsheet.Cells(J, 5)
            Destsheet.Cells(Cell.Row, 6) = Impsheet.Cells(J, 6)

            Impsheet.Cells(J, 1).EntireRow.Delete

            Updated = True ' set the flag

        End If
    Next Cell

    If Updated = False Then 'Existing row not found

        'Copy Data for new Worksheet
        Impsheet.Cells(J, 1).EntireRow.Copy Destsheet.Cells(dest_last_row + 1, 1)
        'Delete the Copied data
        Impsheet.Cells(J, 1).EntireRow.Delet

    End If

Next J

End Sub

So I loop through SFExport (as you did in Module 2) but change my code to set a new variable "Updated" (in bold above) when a value is found (since you're running with several rows), If it isn't found (and revised in Destination) then the row is copied. Either way, the row in SFExport is deleted and the row counter J reduced.

You'll need to run Module 1 from the VB Editor.

Hope this is what you need.

'Your code to update row (= Cell.Row) 
...
' Your code to add new row (= LastRow+1)