on cell change Copy PasteValues before Enter is pressed

bribin · Jul 12, 2018

Greetings,

Re: Excel Macro
Ideally when I change any one of 3 cell I would like a dynamic range to
(PasteSpecial) Preserve entries prior to todays date BEFORE pressing Enter
Previous entries (if any) are formula values

My flawed attempt below does PasteSpecial the Range BUT
it is populated with the new value when Enter is pressed.

Is it possible for Copy PasteSpecial to action; when amounts (eg. 123.45 or 12.34) are typed into KeyCells, before Enter is pressed?
Your help/solution would be greatly appreciated,
Kind regards,
John

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("w21:w23")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

' Identify the Dynamic Range
Dim R As Range
Dim LastDiff As Date
Dim LastCell As Range
LastDiff = DateSerial(9999, 1, 1)
For Each R In Range("Aa6:Aa154") '<<< CHANGE
If Abs(R.Value - Now) < LastDiff Then
Set LastCell = R
LastDiff = Abs(R.Value - Now)
End If
Next R

' Extend the Range
lcell = LastCell.Address
Mid(lcell, 3, 1) = "G"

' Copy and Paste Values ONLY if there are entries prior to todays date
If Now > Range("aa6") Then
Range("AB6:" & lcell).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

End Sub

on cell change Copy PasteValues before Enter is pressed

bribin

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

on cell change Copy PasteValues before Enter is pressed

bribin

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock