Automatically move cell values

[ces102480]

I need to update cells automatically when additional data is entered. An example would be in Column A1="1", A2="2", A3="3". If Type "4" into A1 how would i get the previous data from cell A1, A2 and A3 to automatically drop 1 row without losing that data. Then need to apply across multiple columns. Thank you!

 

[kevin9999]

Not sure what you mean by this:

Then need to apply across multiple columns.

Do you mean you want to insert an entire row wherever you added the new value (i.e. across all columns on the sheet) or do you just mean you want this to apply to all columns in the sheet & just move that column down (where you made the change)? In either case, put the code in the worksheet module of the sheet in question by right-clicking the sheet tab name, select View Code, and place the code in the window that appears on the right of screen. You'll need to save your file as macro-enabled thereafter.

If you want all columns to move down use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
        NewVal = Target.Value
        Application.Undo
        OldVal = Target.Value
        Target.EntireRow.Insert shift:=xlDown
        Target.Offset(-1) = NewVal
        Application.EnableEvents = True
    End If
End Sub

If you just want that one column to shift down use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
        NewVal = Target.Value
        Application.Undo
        OldVal = Target.Value
        Target.Insert shift:=xlDown
        Target.Offset(-1) = NewVal
        Application.EnableEvents = True
    End If
End Sub

 

[Peter_SSs]

If Type "4" into A1 how would i get the previous data from cell A1, A2 and A3 to automatically drop 1 row without losing that data.

Welcome to the MrExcel board!
Would it work for you if the data was moved down before you entered the new data in the top row? If so, you could try this Worksheet_SelectionChange event code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If ActiveCell.Row = 1 Then ActiveCell.Insert Shift:=xlDown
End Sub

 

[ces102480]

Not sure what you mean by this:

Do you mean you want to insert an entire row wherever you added the new value (i.e. across all columns on the sheet) or do you just mean you want this to apply to all columns in the sheet & just move that column down (where you made the change)? In either case, put the code in the worksheet module of the sheet in question by right-clicking the sheet tab name, select View Code, and place the code in the window that appears on the right of screen. You'll need to save your file as macro-enabled thereafter.

If you want all columns to move down use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
        NewVal = Target.Value
        Application.Undo
        OldVal = Target.Value
        Target.EntireRow.Insert shift:=xlDown
        Target.Offset(-1) = NewVal
        Application.EnableEvents = True
    End If
End Sub

If you just want that one column to shift down use this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
        NewVal = Target.Value
        Application.Undo
        OldVal = Target.Value
        Target.Insert shift:=xlDown
        Target.Offset(-1) = NewVal
        Application.EnableEvents = True
    End If
End Sub

I just needed the 1 column to shift down. The 2nd one was what i was looking for, thank you! If i just wanted this to apply to a specific set of cells on the spreadsheet can I do that. For example if i wanted to start on cell E5 through J5 and work from there. Also is there a way to move cells up vs down? Thanks again for the help!

 

[kevin9999]

Here's the code to shift down for just E5:J5. Moving cells up is a much more complicate issue.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("E5:J5"), Target) Is Nothing Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
        NewVal = Target.Value
        Application.Undo
        OldVal = Target.Value
        Target.Insert shift:=xlDown
        Target.Offset(-1) = NewVal
        Application.EnableEvents = True
    End If
End Sub