Identify if string exists, copy entire row.

Hey all,

I am a bit stuck on how to go about this.

I have a range of data on sheet1 that holds employees data, I need to constantly update this and add employees data as they are employed. I import a file that has all the recently added employees(Sheet 2) and I need to search through sheet 1 and see if that persons data is on sheet 2. If it exists on sheet 2 then move to the next employee. When I find an employee that has no no data on sheet1 , copy the entire row.

Here is a sample WB.

Add a Helper Column and add this formula in row 2.

=IF(COUNTIF(Sheet2!A:A,A2),"","copy")

Copy down.

Filter on the Helper Column.
Select the visible rows and copy them
Paste them into sheet 2

You could record a macro while you do this to get the basic code but it would need generalising.

Regards, TMS

The formula ...

will vba be ok .. you could try this old classic code

option explicit
Sub ptest()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long, i As Long, found As Range
    Application.ScreenUpdating = False
    With ThisWorkbook
        Set ws1 = .Sheets("Sheet1")
        Set ws2 = .Sheets("Sheet2")

    End With
    lastRow = ws1.Cells(Rows.Count, "a").End(xlUp).Row
    For i = 1 To ws1.Cells(Rows.Count, "a").End(xlUp).Row
       Set found = ws2.Range("a:a").Find(ws1.Cells(i, "a"), LookIn:=xlValues, LookAt:=xlWhole)
        If Not found Is Nothing Then
         ws1.Cells(i, "A").Resize(, 9).Copy Destination:=ws2.Cells(found.Row, "a")

        End If
    Next i
    Set ws2 = Nothing: Set ws1 = Nothing
    Set found = Nothing
    Application.ScreenUpdating = True
End Sub

Re-reading your first post ...

I need to search through sheet 1 and see if that persons data is on sheet 2

Regards

No worries

this updates sheet2 from sheet1 and if it not in sheet1 and no sheet 2 it adds it to sheet2 for

Option Explicit
Sub ptest()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long, i As Long, found As Range
    Application.ScreenUpdating = False
    With ThisWorkbook
        Set ws1 = .Sheets("Sheet1")
        Set ws2 = .Sheets("Sheet2")
    End With
    lastRow = ws1.Cells(Rows.Count, "a").End(xlUp).Row
    For i = 1 To ws1.Cells(Rows.Count, "a").End(xlUp).Row
        Set found = ws2.Range("a:a").Find(ws1.Cells(i, "a"), LookIn:=xlValues, LookAt:=xlWhole)
        If Not found Is Nothing Then
            ws1.Cells(i, "A").Resize(, 9).Copy Destination:=ws2.Cells(found.Row, "a")
        Else
            ws1.Cells(i, "A").Resize(, 9).Copy Destination:=ws2.Cells(ws2.Rows.Count, "a").End(xlUp).Offset(1, 0)
        End If
    Next i
    Set ws2 = Nothing: Set ws1 = Nothing
    Set found = Nothing
    Application.ScreenUpdating = True
End Sub

Hey Pike,

I can't seem to get your code to work.

I have attached a workbook.

Never Mind, I re-read your code a found what "I" was doing wrong.

alls good then dave .. what was wrong?