VBA - Entering The Date When Data is copy/pasted.

[mrcyvr]

Hello,

I have 1. A Module, Then Worksheet 1, and Worksheet 2.

When the module runs, it transfers data with todays date - from worksheet1 to worksheet 2. I figured out all that yesterday. Today, in VBE - in Worksheet1 I have a code (I provided below), that each time a user hits 'enter' the date gets entered into the next row.

The problem Im having is...Allot of the time staff will not be hitting the 'enter' key to go to the next row. They will be copying the data into the sheet - so Im wondering, is there a way to have the date entered even when the data is copied into the sheet? Or maybe there is a way to combine what is in sheet1 to Module1?

Here is Sheet1:

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 18)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub

Here is what is in Module 1 (not sure if it helps) - but Im not having any issue with Module 1, just the question I described above:

Sub extractdatabasedondate()
Dim LastRow As Long, erow As Long, i As Long
Dim mydate As Date
LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range("A1").Select
For i = 2 To LastRow
mydate = Cells(i, 18)
If mydate = Date Then
erow = Sheet2.Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).Row
Range(Cells(i, 1), Cells(i, 18)).Copy Destination:=Sheets("sheet2").Cells(erow, 1)
End If
Next i
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_change(ByVal Target As Range)
'Modified  9/12/2018  12:57:58 AM  EDT
If Target.Column <> 1 Then Exit Sub
With Target.Offset(0, 18)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub