excel - Pasting Today's date along with new range of data when macro is run

Question

I have a file that uses a vba code that when a button is clicked, it copies names from one sheet and paste it below the last cell of another. The code works, but I also want to be able to paste the current date in the corresponding column when those names are populated. I thought about making another column with Excel's Today() function, but that ends up pasting the formula instead of the value. Is there a better way to just paste the date into a range of cells?

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
    'Set variables for copy and destination sheets
  Set wsCopy = ThisWorkbook.Worksheets("Site Specific")
  Set wsDest = ThisWorkbook.Worksheets("Manual Time Collection")
'1. Find last used row in the copy range based on data in column B employee name
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
  
'2. Find first blank row in the destination range based on data in column B employee name
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
wsCopy.Range("B2:B" & lCopyLastRow).Copy _
  wsDest.Range("B" & lDestLastRow)
wsCopy.Range("G2:G" & lCopyLastRow).Copy _
  wsDest.Range("G" & lDestLastRow)
wsCopy.Range("H2:H" & lCopyLastRow).Copy _
  wsDest.Range("E" & lDestLastRow)

End Sub

Sheet to copy from sheet1

Sheet to paste in sheet2

question from:https://stackoverflow.com/questions/65930756/pasting-todays-date-along-with-new-range-of-data-when-macro-is-run

Answer 1

Waitting for answers