How do you make Data move into another cell??

[Kyrie]

I need some help as i have been looking everywhere and cannot find a sloution.
My boss would like to have a spreadsheet full of lots of different numbers
but when you start to write over the top, the data from the cell moves in to
the cell next to it. So it will be almost like pushing all the numbers into
the cell next to it without inserting an extra row or column.
I hope this makes sense and any help would be greatly appreciated

 

[Don Guillett]

Right click sheet tab>view code insert this.
Now when you change any value in col A the rest of the row moves over one to
the right and the value moves to the cell to the right.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
Target.Offset(, 1).Resize(, lc).Cut Target.Offset(, 2)
Target.Offset(, 1) = Target
End Sub

 

[Fred Smith]

Why not just use Insert? It will push the cells over. Just right-click on
the cell, choose Insert... then choose Shift cells right (or down). You're
always better off using Excel the way it was designed.

Regards,
Fred

 

[Kyrie]

This is exactly what i needed!
Thank you so much

Right click sheet tab>view code insert this.
Now when you change any value in col A the rest of the row moves over one to
the right and the value moves to the cell to the right.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
Target.Offset(, 1).Resize(, lc).Cut Target.Offset(, 2)
Target.Offset(, 1) = Target
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Using the other suggestion, this also works to make it automatic

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.Insert Shift:=xlToRight
Application.EnableEvents = True
End Sub