Compare [Sheet 2] to [Sheet 1] & add any missing unique numbers in [Sheet 1] to the bottom of table in [Sheet 2]

Question

Just wondering if anybody could help with the below problem: I have two sheet:

1. Mini Master
2. Critical Path

New data is added to the mini master on daily basis. Both the Mini Master Critical Path hold the unique number in column A.

I would like to run a macro that compares Column A in the Mini Master to column A in the Critical Path.

If the Mini Master has any unique numbers (that are not listed in column A of the Critical Path) I would like to copy & paste them to the bottom of the Critical Path Table.

Data should only flow Mini Master > Critical path. Never Critical Path > Mini Master.

Example: it would be great if i could find a macro that identifies the missing unique numbers such as the one (highlighed in pink) in the below image.

Mini Master Sheet

Then copy & paste that unique number onto the bottom of the table on the Critical Path Sheet (also highlighted in pink)

Critical Path Sheet

Once the data is in i have written a code that will keep the Critical Path Sheet Up to date with any changes made in the Mini Master, that will then populate columns B, C & D.

Thank-you in advance for your help.

Answer 1

Try,

Sub test()
    Dim Ws As Worksheet
    Dim toWs As Worksheet
    Dim rngDB As Range, rngT As Range
    Dim vDB As Variant, vR() As Variant
    Dim i As Long, n As Long, j As Integer

    Set Ws = Sheets("MINI MASTER")
    Set toWs = Sheets("CRITICAL PATH")

    vDB = Ws.Range("a1").CurrentRegion
    With toWs
        Set rngDB = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
        For i = 2 To UBound(vDB, 1)
            If WorksheetFunction.CountIf(rngDB, vDB(i, 1)) Then
            Else
                n = n + 1
                ReDim Preserve vR(1 To 4, 1 To n)
                For j = 1 To 4
                    vR(j, n) = vDB(i, j)
                Next j
            End If
        Next i
        Set rngT = .Range("a" & Rows.Count).End(xlUp)(2)
        If n Then
            rngT.Resize(n, 4) = WorksheetFunction.Transpose(vR)
        End If
    End With
End Sub