how to stay same cell after hit enter and move cell value to down

Question

I need to stay at the same cell in the excel sheet and copy the cell value to down after hitting enter. need to keep the most recent value at the top and shift one by one down(the red arrow shows the current cell and the green arrow shows the most recent value). is there any way to do this with VBA programming?

Answer 1

If you add this code to the Worksheet_Activate() event procedure

Private Sub Worksheet_Activate()
    Application.MoveAfterReturn = False
End Sub

so that the active cell will not change after data entry then you could code the Worksheet_Change() event for your data-entry tab as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        With Application
            .EnableEvents = False
            Target.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            .EnableEvents = True
        End With
    End If
End Sub

and also add the code below the Worksheet_Deactivate() procedure

Private Sub Worksheet_Deactivate()
    Application.MoveAfterReturn = True
End Sub

so that the normal active-cell-move-on-Enter behaviour is restored on other sheets.