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?
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:
Open the workbook and click on the worksheet where you would like this task done.
Right click on the worksheet's tab and click View Code.
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
Verify that the document is for the correct sheet and then close the code editor.
I'll explain this step by step:
Private Sub Worksheet_Change(ByVal Target As Range)
indicates that this code should run whenever a cell is changed in the worksheet.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.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.If Not Target.Value = "" Then
checks if the value of the changed cell is an empty string. This way nothing changes when you delete.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.Here's a visual in case that's helpful:
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP