--
Ken living downunder


"Ken Johnson" wrote:

Hi Ken,

This version only requires one simple change to change the cell used as
the input cell.
For example, if C55 is your input cell then you only have to change the
first line to...

Dim rngInput As Range: Set rngInput = Range("C55")

Private Sub Worksheet_Change(ByVal Target As Range)
'Change the address string below to suit your needs
Dim rngInput As Range: Set rngInput = Range("B3") '<<<
If Not Intersect(Target, rngInput) Is Nothing Then
If Cells(Rows.Count, rngInput.Column).End(xlUp).Row = _
rngInput.Row And rngInput.Offset(1, 0).Value < "" Then
MsgBox "You have reached the bottom of the sheet!"
Exit Sub
End If
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
Dim rngOld As Range, rngNew As Range
Set rngOld = Range(rngInput, Cells(Cells(Rows.Count, _
rngInput.Column).End(xlUp).Row, rngInput.Column))
Set rngNew = rngOld.Offset(1, 0)
rngNew.Value = rngOld.Value
With rngInput
.ClearContents
.Select
End With
Application.EnableEvents = True
End If
Exit Sub
ERRORHANDLER: Application.EnableEvents = True
End Sub


Ken Johnson


Hi Ken again

Thanks for your follow up

Best regards

Ken Pearson