A Worksheet Change: Copy Each Entered Value to a Column

• The following screenshot illustrates what has happened in Sheet2 after entering 1,2,3,4 and 5 into cell A1 of Sheet1.

• Copy the following code into the sheet module of the source worksheet (Sheet1), the one where you will be entering the values.
  (In the Project Explorer, double-click on Sheet1(Sheet1), and the correct window will open.)
• Observe the blue-ish title bar where it shows ... Sheet1 (Code): note that this Sheet1 refers to the Sheet1 on the left (in the Project Explorer) that is not in parentheses. It is the sheet s code name. The name in parentheses is the tab name (the one you see in Excel) and it could be different.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
      Define constants.
    Const SRC_CELL As String = "A1"
    Const DST_SHEET As String = "Sheet2"
    Const DST_FIRST_CELL As String = "A2"
    
      Reference the source cell.
    Dim sCell As Range: Set sCell = Me.Range(SRC_CELL)
      Check if the source cell was not changed.
    If Intersect(sCell, Target) Is Nothing Then Exit Sub
    
      Reference the first destination cell.
    Dim dCell As Range:
    Set dCell = Me.Parent.Sheets(DST_SHEET).Range(DST_FIRST_CELL)
    
    With dCell
          Reference the destination range, the range starting from
          the first destination cell to the last cell in the column
          i.e. e.g. for  A2  this means the range  A2:A1048576 .
        Dim drg As Range: Set drg = .Resize(Me.Rows.Count - .Row + 1)
          Attempt to reference the last (bottom-most) non-empty cell
          of the destination range.
        Dim dlCell As Range:
        Set dlCell = drg.Find("*", , xlFormulas, , , xlPrevious)
          If the attempt was succesful i.e. there is a non-empty cell,...
        If Not dlCell Is Nothing Then
              ... check if the last cell is not the last cell
              of the destination range (there is no cell below it).
            If dlCell.Row = Me.Rows.Count Then Exit Sub
              Reference the cell below the last cell.
            Set dCell = dlCell.Offset(1)
         Else   If the attempt failed i.e. all cells are empty,...
              ... do nothing i.e. use the already referenced destination cell.
        End If
    End With
    
      Write the value from the source cell to the destination cell.
    dCell.Value = sCell.Value
    
End Sub