So i currently have a report that i process some raw data from a external system.
Using AdvancedFilter i copy what is needed from the raw data into the report into a "Clean" tab ( This is where i plan on doing all the manipulations needed) before moving it to the final sheet that the reports and graphs are pulling from.

My ask is i need to ONLY move records from the "Clean" tab to the "Final" tab if they don't already exist in that tab. (there is 1 key column that contains a alphanumeric value that is unique)

What I'm looking for is some direction on what is best or most efficient to look in the "Final" tab at those values and then ONLY copy rows from "Clean" that do not exist.

im currently using this in another report and wondering if this is the way to go, if this would work, what are the areas i would need to focus on changing in order to loop thru all existing values in the "Final" and copy only ones that dont exist from the clean.

Private Sub SelectCopyCleanData()

    Dim shMain As Worksheet, shClean As Worksheet
    Dim LastRow As Long
    
    'Set variables for copy and destination sheets
    Set shMain = ThisWorkbook.Worksheets("MainRecords")
    Set shClean = ThisWorkbook.Worksheets("Cleanup")
    
    ' THIS GET A TOTAL COUNT OF ROWS BASED ON CLAIM NUMBER
    LastRow = shMain.Range("A" & Rows.Count).End(xlUp).Offset(1).Row

    Dim arr As Variant
    With shClean.Range("A2").CurrentRegion
        arr = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    
    Dim i As Long, j As Long
    
    shMain.Cells(LastRow, 1).Resize(UBound(arr), UBound(arr, 2)).Value = arr
    
    ThisWorkbook.Worksheets("Cleanup").Rows("2:" & Rows.Count).ClearContents
    
End Sub
Tabs:
"Final" = historical/current records (Unique Column Value is alphanumeric, ex. PHH290483)
"Clean" = Todays/Weekly report records (Also has the same unique value)

Once i have run any clean up on the Clean tab, then i would like to copy any records from the "Clean" tab into the "Final" tab that DO NOT exist in the "Final"


Im working on cleaning up the current file, but may take a bit to get that ready..
So the ask is more on how can i modify the above array copy paste to have that condition so that the array that is built is only the one that do not exist.