VBA: Copy & Paste to another sheet if condition met AND a value in the range is not already present in the target sheet

[hwatson86]

Hi All,
I am attempting to help our recruitment team test a new hiring process which requires an excel spreadsheet to manage.

This is what I would like to achieve.

1. Every day, the recruiters will copy & paste the most recent applicants from our tracker system into a sheet called Sheets("UPLOAD Candidates") (overriding existing entries)
2. Once done, they will click a button called 'Update Shortlist' (I know how to achieve this part)
3. Candidates who meet the criteria ("YES" value in column AC), cell values of particular columns (named ranges) will be be pasted into columns A:G of the target sheet "Shortlist" (starting at A7)
4. The workbook automatically saves at this point

Rules that must be in place:
1. If the candidate already exists (if value in Column J of "Shortlist" matches a value in Column AF of "UPLOAD Candidates" - DO NOT COPY & PASTE
2. Values in Shortlist should not be overridden, new candidates who meet the criteria should be appended to the existing shortlist

Other things to note:
AC column in Sheets("UPLOAD Candidates) have a formula which drives the YES, don't need the formula copied, just the value



I started with this, which achieves the pasting of ranges, just not the 'meeting of conditions' or checking if a value is already present

Sub Candidate_Save()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim DestRow As Long
    
On Error GoTo ErrHandler


    Set ws1 = Sheets("UPLOAD Candidates")
    Set ws2 = Sheets("Shortlist")
    DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws2.Range("A" & DestRow).Value = ws1.Range("Shortlist_Jobs").Value
    ws2.Range("B" & DestRow).Value = ws1.Range("First_Name").Value
    ws2.Range("C" & DestRow).Value = ws1.Range("Last_Name").Value
    ws2.Range("D" & DestRow).Value = ws1.Range("Email_Address").Value
    ws2.Range("E" & DestRow).Value = ws1.Range("Phone").Value
    ws2.Range("f" & DestRow).Value = ws1.Range("Mobile").Value
    ws2.Range("g" & DestRow).Value = ws1.Range("ApplyDate").Value


Exit Sub
ErrHandler:
    Error_Capture
    
End Sub

Thanks in advance