Copy rows from Sheet2 that don't exist on Sheet1

[xcl_usr]

Daily I get cases added to my queue. I would like to take my case dump (exported to Excel) and intigrate it to a formated sheet that I work from during the day. I don't have a way to just grab new cases in the export. I can paste the export to Sheet2. I'll use a macro to delete unneeded columns, then I need to walk Sheet2 and if it does not exist in the list of Col1 on Sheet1, then copy it to Sheet1. After which I will use a macro to sort and add conditional formattitng on Sheet1.

Exported worksheet (Pasted to Sheet2)
-----------------------
Col1 Col2 Col3 Col4
100 P2 W Desc1
101 P3 W Desc2
102 P1 W Desc3

Formatted worksheet (Sheet1)
-----------------------
Col1 Col2 Col3 Col4 Notes
98 P2 W DescA Escalate
99 P2 H DescB Call Back
100 P2 W Desc1 Send email
101 P3 W Desc2 Sent email

Note that record 100 and 101 exist already on Sheet1, so I don't want to copy them from Sheet2.

Bonus points - Add new rows, and update just certain cells Col2 changed, and retain special cells on Sheet1 like Notes.

Help!

Tony

 

[xcl_usr]

Shameless bump

 

[vconfused]

Something like this?

Option Explicit

Sub TransferNewData()
Const strCol1 = "A" 'assumes columns 2-4 are directly to the right of 1
Const strSheet1Name = "Sheet1"
Const strSheet2Name = "Sheet2"
Const lngFirstRowDataSheet1 = 1
Const lngFirstRowDataSheet2 = 1

Dim lngLastRowSht1 As Long, lngLastRowSht2 As Long
Dim sht1 As Worksheet, sht2 As Worksheet
Dim strOrigSheet As String, strOrigSelectionSht1 As String, strOrigSelectionSht2 As String
Dim cell As Range
Dim varMatch

    Application.ScreenUpdating = False
    strOrigSheet = ActiveSheet.Name
    Set sht1 = Sheets(strSheet1Name)
    Set sht2 = Sheets(strSheet2Name)
    
    sht1.Select
    strOrigSelectionSht1 = Selection.Address
    sht2.Select
    strOrigSelectionSht2 = Selection.Address
    
    lngLastRowSht1 = sht1.Range(strCol1 & 65536).End(xlUp).Row
    lngLastRowSht2 = sht2.Range(strCol1 & 65536).End(xlUp).Row
    
    sht2.Select
    For Each cell In sht2.Range(strCol1 & lngFirstRowDataSheet2 & ":" & strCol1 & lngLastRowSht2)
        On Error Resume Next
        varMatch = WorksheetFunction.VLookup(cell, sht1.Range(strCol1 & lngFirstRowDataSheet1 & ":" & strCol1 & lngLastRowSht1), 1, False)
        If Err <> 0 Then
            Err.Clear
            On Error GoTo 0
            sht2.Range(cell, cell.Offset(0, 3)).Select
            Selection.Copy
            lngLastRowSht1 = lngLastRowSht1 + 1
            sht1.Select
            sht1.Range(strCol1 & lngLastRowSht1).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            sht2.Select
        End If
    Next
    
    On Error GoTo 0
    
    sht1.Select
    Range(strOrigSelectionSht1).Select
    sht2.Select
    Range(strOrigSelectionSht2).Select
    
    Set sht1 = Nothing
    Set sht2 = Nothing
    Sheets(strOrigSheet).Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Just goes through Col1 in sheet2 til it finds a value not in sheet1 in col1 then it copies and pastes the values (so as not not distrupt formatting), then continues on.