0

I have to copy or update cell value to new adjacent cell every time when reference cell value is updated.

I have Cell A1 with some formula, which updates it value frequently. what i need to do is, every time when value of A1 gets updated, that value should be copied in new adjacent cell.

if A1 is 20, then B1 should be 20.

Again if A1 id updated to new value 23, then B1 should be 20 but C1 should be 23,so on.

update 1: A1=20  B1=20
update 2: A1=23  B1=20   C1=23
update 3: A1=25  B1=20   C1=23  D1=25

1 Answer 1

0

This small Event macro will place the latest A1 value at the end of the first row:

Private Sub Worksheet_Calculate()
    Dim v As Variant, N As Long

    v = Range("A1").Value
    Application.EnableEvents = False
    If Range("B1").Value = "" Then
        Range("B1").Value = v
        Application.EnableEvents = True
        Exit Sub
    End If

    N = Cells(1, Columns.Count).End(xlToLeft).Column
    If Cells(1, N).Value = v Then
        Application.EnableEvents = True
        Exit Sub
    End If

    Cells(1, N + 1).Value = v
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

1
  • Gary thanks earlier but would you mind telling every steps detailed as i am new to macro or VBA Mar 31, 2015 at 10:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .