Worksheet Change trigger to identify last empty row in another table on another sheet and enter looked up data

Table to Table on Change

• When dealing with Excel tables,
  • there is no need to envolve worksheet rows or columns,
  • you can reference the 'first available' row by adding a new one.

Private Sub WorksheetChange(ByVal Target As Range)
    ' Constants
    Const STABLENAME As String = "Table1"
    Const SCHANGECOLUMNNAME As String = "T-0 Date"
    Const SLONGITUDECOLUMNNAME As String = "Longitude"
    Const DSHEETNAME As String = "Sheet3"
    Const DTABLENAME As String = "T0Change"
    Const DDATESTAMPCOLUMNNAME As String = "When T-0 date changed"
    Const DCHANGECOLUMNNAME As String = "T-0 Date"
    Const DLONGITUDECOLUMNNAME As String = "Longitude"
    Const MAXCHANGEDCELLSCOUNT As Long = 100
    ' Stamp
    Dim Stamp As Date: Stamp = Date ' i.e. Today
    ' Source
    Dim watchTable As ListObject: Set watchTable = Me.ListObjects(STABLENAME)
    Dim watchRange As Range: Set watchRange = 
        watchTable.ListColumns(SCHANGECOLUMNNAME).DataBodyRange
    ' Reference the changed cells.
    Dim changedCells As Range: Set changedCells = Intersect(watchRange, Target)
    If changedCells Is Nothing Then Exit Sub
    If changedCells.Cells.Count > MAXCHANGEDCELLSCOUNT Then Exit Sub
    ' Reference the table rows of the changed cells.
    Dim changedRows As Range: Set changedRows = 
        Intersect(watchTable.DataBodyRange, changedCells.EntireRow)
    ' Retrieve the source column indices.
    Dim sChangeColumn As Long, sLongitudeColumn As Long
    With watchTable
        sChangeColumn = .ListColumns(SCHANGECOLUMNNAME).Index
        sLongitudeColumn = .ListColumns(SLONGITUDECOLUMNNAME).Index
    End With
    ' Using the changed rows and the column indices, you can easily reference
    ' the required cells by using the 'Cells' property (see 'crrg' below).
    ' No need to use worksheet rows or columns.
    ' Destination
    Dim destTable As ListObject: Set destTable = 
        Me.Parent.Sheets(DSHEETNAME).ListObjects(DTABLENAME)
    ' Reference the first available row range.
    Dim drrg As Range ' (current destination row range)
    If destTable.ListRows.Count = 0 Then ' empty table
        Set drrg = destTable.HeaderRowRange.Offset(1)
    Else
        Set drrg = destTable.ListRows.Add.Range
    End If
    ' Retrieve the destination column indices.
    Dim dStampColumn As Long, dChangeColumn As Long, dLongitudeColumn As Long
    With destTable
        dStampColumn = .ListColumns(DDATESTAMPCOLUMNNAME).Index
        dChangeColumn = .ListColumns(DCHANGECOLUMNNAME).Index
        dLongitudeColumn = .ListColumns(DLONGITUDECOLUMN_NAME).Index
    End With
    ' Using the current row and the column indices, you can easily reference
    ' the required cells by using the 'Cells' property. Use the 'Offset'
    ' property to reference the next row on each iteration (see 'drrg' below).
    ' No need to use worksheet rows or columns.
    ' Copy
    Dim crrg As Range ' current source (changed) row range
    For Each crrg In changedRows.Rows
        drrg.Cells(dStampColumn).Value = Stamp
        drrg.Cells(dChangeColumn).Value = crrg.Cells(sChangeColumn).Value
        drrg.Cells(dLongitudeColumn).Value = crrg.Cells(sLongitudeColumn).Value
        Set drrg = drrg.Offset(1) ' next destination row range
    Next crrg
End Sub