I have the following code, which works terrificially.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F18:F450"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, -5).Value = Format(Date, "dd-mmm-yyyy")
End If
End With

End If

Const WS_RANGE_1 As String = "F18:L450"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
With Target
If .Value <> "" Then
Range("B2").Value = Format(Date, "dd-mmm-yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

I use it to update a cell with the current date when something in the
specific range changes. What I normally do on this particular sheet is to
paste values in, because others update the information on the spreadsheet.
What I would like to happen is that when I paste information in from the
clipboard that the date in B2 gets updated with the current date after the
paste.

What do I need to do to modify my code to allow that to happen?

thanks for letting me know.
Terry

Have you looked at the Worksheet_Activate or Worksheet_Deactivate events to
see if they would do the trick?

"Harddrive747" wrote:

> I have the following code, which works terrificially.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "F18:F450"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> If .Value <> "" Then
> .Offset(0, -5).Value = Format(Date, "dd-mmm-yyyy")
> End If
> End With
>
> End If
>
> Const WS_RANGE_1 As String = "F18:L450"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
> With Target
> If .Value <> "" Then
> Range("B2").Value = Format(Date, "dd-mmm-yyyy")
> End If
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> I use it to update a cell with the current date when something in the
> specific range changes. What I normally do on this particular sheet is to
> paste values in, because others update the information on the spreadsheet.
> What I would like to happen is that when I paste information in from the
> clipboard that the date in B2 gets updated with the current date after the
> paste.
>
> What do I need to do to modify my code to allow that to happen?
>
> thanks for letting me know.
> Terry

I put this code into both places and it did not work. I did see a post
somewhere and for the lift of me, I can't find it that someone use some type
of command that he said it updated a cell even when a paste happened.

Thanks.

"JLGWhiz" wrote:

> Have you looked at the Worksheet_Activate or Worksheet_Deactivate events to
> see if they would do the trick?
>
> "Harddrive747" wrote:
>
> > I have the following code, which works terrificially.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "F18:F450"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > If .Value <> "" Then
> > .Offset(0, -5).Value = Format(Date, "dd-mmm-yyyy")
> > End If
> > End With
> >
> > End If
> >
> > Const WS_RANGE_1 As String = "F18:L450"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
> > With Target
> > If .Value <> "" Then
> > Range("B2").Value = Format(Date, "dd-mmm-yyyy")
> > End If
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > I use it to update a cell with the current date when something in the
> > specific range changes. What I normally do on this particular sheet is to
> > paste values in, because others update the information on the spreadsheet.
> > What I would like to happen is that when I paste information in from the
> > clipboard that the date in B2 gets updated with the current date after the
> > paste.
> >
> > What do I need to do to modify my code to allow that to happen?
> >
> > thanks for letting me know.
> > Terry