TransWikia.com

Excel Copy and keep

Super User Asked by Kyle Smith on December 11, 2021

I want to have excel copy the contents of "B1" to "A1" and keep the contents in "A1" even if "B1" is deleted. Is this possible?

2 Answers

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:

enter image description here

Answered by Worthwelle on December 11, 2021

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.

Answered by Gary's Student on December 11, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP