Copy & Paste with a VBA TimeStamp

[Marsman]

Hello folks - The following VBA code works great... the issue I am having is when someone does a Copy and Paste in Column K, the Time Stamp that is set for Column E will not do anything unless the value is entered one at a time.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K:K")) Is Nothing Then

On Error Resume Next
    If Target.Value = "" Then

      Target.Offset(0, -6) = ""
    Else

      Target.Offset(0, -6).Value = Format(Now, "mm/dd/yyyy hh:mm:ss")

    End If

  End If

End Sub

Can someone explain to me how I could the TimeStamps show in Column E when I paste in multiple lines under Column K


Thanks

 

[Joe4]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, r As Range
    
    Set rng = Intersect(Target, Range("K:K"))
    
    If rng Is Nothing Then Exit Sub
    
'   Loop through each row in range
    For Each r In rng
        Application.EnableEvents = False
        If r.Value = "" Then
            r.Offset(0, -6) = ""
        Else
            r.Offset(0, -6).Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
        End If
        Application.EnableEvents = True
    Next r

End Sub

 

[Marsman]

That is a beautiful thing....

Thanks for the help.

 

[Joe4]

You are welcome!