First, make sure that your two Tables are indeed Excel Tables. (If not, select them one at a time and use the Ctrl + T keyboard shortcut to turn them into 'official' Excel Tables aka ListObjects)
Then select column A in Table1, and assign the named range "Primary" to it by writing "Primary" in the name box as shown below and then push Enter:
Likewise give column A in Table2 the name "Secondary".
Note that these names are case sensitive, because we're going to be referencing them from VBA.
Put it in the Sheet Module corresponding to the worksheet that Table 1 is in.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dic As Object
Dim v1 As Variant
Dim v2 As Variant
Dim vItem As Variant
Dim lo As ListObject
Dim lr As ListRow
Dim lc As ListColumn
On Error GoTo errhandler
If Not Intersect(Range("Primary"), Target) Is Nothing Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set dic = CreateObject("Scripting.Dictionary")
v1 = Range("Primary")
v2 = Range("Secondary")
Set lo = Range("Secondary").ListObject
Set lc = lo.ListColumns(1)
For Each vItem In v2
If Not dic.exists(vItem) Then
dic.Add vItem, vItem
Else
MsgBox "You have " & vItem & " in the table already!. Please rename the row and try again."
GoTo errhandler
End If
Next vItem
For Each vItem In v1
If Not dic.exists(vItem) Then
Set lr = lo.ListRows.Add
Intersect(lr.Range, lc.Range).Value = vItem
End If
Next vItem
End If
errhandler:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
From now on, anything new you add to the Primary column will be added to the Secondary column:
