'All Sheets' Code
Option Explicit
'This Workbook Code
Private Sub Workbook_Open()
SetDates
End Sub

Private Sub SetDates()
    Sheets("Sheet1").Range("A1").Value = Format(Date, "dd/mm/yyyy")
    Sheets("Sheet2").Range("A1").Value = Format(Date - 1, "dd/mm/yyyy")
End Sub
'Sheet1 Code
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Columns(3), Target) Is Nothing Then CopyPaste Target
End Sub

Private Sub CopyPaste(Target)
    Dim NextPasteCell As Range, NewRows As Range
    
    Set NewRows = Intersect(Columns(3), Target).Offset(0, -1)
    Application.EnableEvents = False
   
    NewRows = Date 'NewRows is not Column B of the New Dat
    
    With Sheets("Sheet2")
      NextPasteCell = .Cells(Rows.Count, 2).Offset(1)
      .Range("B:B").ClearContents
    End With
    
    NewRows.Rsize(NewRows.Cells.Count, (Columns("V").Column - NewRows.Coumn) + 1).Copy NextPasteCell
    NewRows = "New"
    
    Application.EnableEvents = True

End Sub