vba import missing values and paste as values

[izk630]

I would like my sheet to check on sheet 2 any doc# (column C) thats not already listed on sheet 1 and copy and paste as values the missing doc# into sheet 1 as well as all columns that match (from colum c to p on sheet 1)

the reason I am not just using an index match is because my sheet 2 is an external source that keeps changing data and past data gets erased when the new one is imported

attaching example sheet.

[alansidman]

Try this:

Option Explicit
Sub Missing()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("1")
    Set s2 = Sheets("2")
    Dim lr As Long, i As Long, lr2 As Long, Res As Variant
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    With s2
        For i = 2 To lr2
            lr = s1.Range("C" & Rows.Count).End(xlUp).Row

            On Error Resume Next
            Err.Clear
            Res = Application.WorksheetFunction.VLookup(.Range("C" & i), s1.Range("C2:C" & lr), 1, False)
            If Err.Number <> 0 Then
                .Range("C" & i).Copy s1.Range("C" & lr + 1)
                .Range("D" & i & ":P" & i).Copy s1.Range("E" & lr + 1)
            End If
        Next i
    End With


End Sub

[izk630]

seems to be working great only thing is that its pasting with formating and I would like it to paste only values dont want to copy color highlights and boarder formatting

[alansidman]

Modified to paste values

Option Explicit
Sub Missing()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("1")
    Set s2 = Sheets("2")
    Dim lr As Long, i As Long, lr2 As Long, Res As Variant
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    With s2
        For i = 2 To lr2
            lr = s1.Range("C" & Rows.Count).End(xlUp).Row

            On Error Resume Next
            Err.Clear
            Res = Application.WorksheetFunction.VLookup(.Range("C" & i), s1.Range("C2:C" & lr), 1, False)
            If Err.Number <> 0 Then
                .Range("C" & i).Copy 's1.Range("C" & lr + 1)
                s1.Range("C" & lr + 1).PasteSpecial xlPasteValues
                .Range("D" & i & ":P" & i).Copy 's1.Range("E" & lr + 1)
                s1.Range("E" & lr + 1).PasteSpecial xlPasteValues
            End If
        Next i
    End With


End Sub

[izk630]

its only pasting C column values not everything else now

[alansidman]

Apologies. Had a typo in the code. Misspelled Special. I have fixed it. Try the code now.

[izk630]

works perfect thanks