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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…