offset one row

[samisamih]

Hello
I want to write a macro event(change) that in every time i insert a number the previous number that i have entered down one row.

for example if i have wrote in cell (a1) the numbers:

1,2,3,4,5

so the number 5"the last number i write" will be in cell (a1), the number 4 will be in cell(a2),the number 3 will be in cell(a3)............

how can i do thats.
thanks

[SamT]

Option Explicit

Private CurrentValue As Variant


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
CurrentValue = Range("A1").Value
Else
On Error Resume Next
Set CurrentValue = Nothing
End If

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = "$A$1" Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not IsNumeric(CurrentValue) Then Exit Sub

Application.EnableEvents = False
With Target
.Offset(1).Insert (xlShiftDown)
.Offset(1).Value = CurrentValue
.Select
End With
Set CurrentValue = Nothing
Application.EnableEvents = True

End Sub