Forum Discussion
If Statement that paste/freezes another cell
My file links an online source for daily Gold Prices.
I need an if statement, where if I enter a date in a certain cell (ie, cell A1), then the calculated Daily Gold Price per Gram in cell A2, should be frozen or pasted, keeping the Gold Price per Gram of that days.
I am not as familiar with VBAs, but any assistance is welcomed.
Thank you.
15 Replies
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 SubThank 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.
