Forum Discussion
If Statement that paste/freezes another cell
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
If Range("A1").Value <> "" Then
Application.EnableEvents = False
Range("A2").Value = Range("A2").Value
Application.EnableEvents = True
End If
End If
End Sub
Thank you good sir.
Question, do I always have to run the macro or once it’s enabled & the file type is updated/saved, then as long as the first condition is met (date inserted in A1), the paste will happen automatically?
also, if I write the macro in tab 1, but duplicate the tab, will the macro now be enabled in tab 2 (copy of tab 1) or would I need to rewrite the macro in the tab 2?
thank you again Hans
The code is not a macro, but a so-called event procedure. Excel will execute it automatically each time you change the value of a cell in that worksheet. (but it will only do something when you enter a value in cell A1). You don't have to run it yourself (actually, you cannot run it since it is not a macro).
The code is stored with the worksheet, so if you duplicate the worksheet, the code will be duplicated to the copy, and run there automatically.
- Thank you Hans.
It worked perfectly, not a big deal, but any reason, when I delete the date, why the previous formula does not repopulate?