Excel Copy and keep

The best way to accomplish this may be through Excel VBA. VBA (Visual Basic for Applications) is a programming language that can be used to automate tasks in Excel and other Office applications.

Since you didn't give us much information, this is a very simple script. You would need to expand it to check for errors. (This won't work if you modify multiple cells at once, for example.)

To do this:

1. Open the workbook and click on the worksheet where you would like this task done.

2. Right click on the worksheet's tab and click View Code.

3. A separate window should open with a blank document. Into that document, insert this code:

   Private Sub Worksheet_Change(ByVal Target As Range)
       Dim KeyCells As Range
       Set KeyCells = Range("B1:B10")
   
       If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
           If Not Target.Value = "" Then
               Target.Offset(0, -1).Value = Target.Value
           End If
       End If
   End Sub
   

4. Verify that the document is for the correct sheet and then close the code editor.

I'll explain this step by step:

1. Private Sub Worksheet_Change(ByVal Target As Range) indicates that this code should run whenever a cell is changed in the worksheet.
2. Dim KeyCells As Range and Set KeyCells = Range("B1:B10") specifies the range that we're checking for changes. In this case I picked B1:B10, but you can change this to whatever you want.
3. If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then checks to see if the changed cell is in the range we want. (i.e. B1:B10 in this case.) If it isn't, then we do nothing.
4. If Not Target.Value = "" Then checks if the value of the changed cell is an empty string. This way nothing changes when you delete.
5. Target.Offset(0, -1).Value = Target.Value copies the value of the changed cell into the cell to its left. That is, if we change B3, then A3 will be updated.
6. The rest just closes things out.

Here's a visual in case that's helpful:

If B1 contains data rather than a formula, just copy B1 and paste into A1.

If B1 contains a formula, then click on B1 and click on Copy. Then click on A1 and click on PasteSpecial Value. This will preserve the current value of B1 in A1.