How do I copy a cell value to another cell?

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 manua

How do I copy a cell value to another cell?

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

::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

Video liên quan