Excel copy cell value to another cell if cell value changes

Question

When a cell (A1) value is changed, it will be copied to another sheet (sheet3) in particular column (c)each time creating new cell. the code works fine if cell value in A1 is manually entered, but here i have formula /paste link in A1. and with this formula, its not automatically updating. Another challenge is I wanted to copied to sheet1 in another excel(Amount) kept in same folder.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
    ' Get the last row on our destination sheet (using Sheet2, col A here)...
    Dim intLastRow As Long
    intLastRow = Sheet3.Cells(Sheet3.Rows.Count, "C").End(xlUp).Row
    ' Add our value to the next row...
    Sheet3.Cells(intLastRow + 1, "C") = Target.Value
End If

End Sub

Answer 1

::Caveat::

The Worksheet Change event works when content in a cell changes, and while the Worksheet Selection Change event works whenever a new cell is selected.

In your case whenever you either enter data in Cell A1 or recalculate it, the Macro copies every new value to another sheet in new row.

But when Cell A1 is linked with formula it doesn't works, the reason is in that case neither Worksheet Change or Selection Change works.

Therefore I would like to suggest Worksheet Calculate event, because it triggers Macro whenever the Sheet calculates any formula, as happens in your case, since Cell A1 is linked with formula cell.

Private Sub CalCopy()

 Workbooks.Open "C:\Yourfolder\satyam.xlsx"
 a = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
 Sheets("Sheet1").Range("A" & a).Value = Workbooks("1.xlsm").Worksheets("Sheet1").Range("A1").Value

  ActiveWorkbook.Save
  Workbooks("satyam.xlsx").Close

 End Sub

---

Private Sub Worksheet_Calculate()

    If Range("A1").Value = Range("H1") Then
      
       Call CalCopy
     End If
    
    End Sub

---

How it works:

• Cell A1 is linked with the Cell H1, has a formula.
• As soon you change data in source cells linked with the formula in Cell H1, Excel gets new values in both Cell H1 & A1.
• And then the Worksheet Calculate event triggers the Macro CalCopy, copies value from Cell A1 into Sheet1 in another Workbook as new row/record .

N.B.

• Use both the VBA macros as Standard Module.
• Save the Workbook as Macro Enabled 1.xlsm.
• You need to change File path as needed.
• You may adjust cell references in the code as needed

Answer 2

Try using the Change event rather than SelectionChange.