Column Offset Update
 

Hi
I have a cell with a DDE link from another program, which is sample data I
need to record.
To do this I need to copy the value of the cell, A1, and paste it into B1,
when the cell value changes I need to copy the new value from A1 into B2 and
so on. I can then produce a real time graph from the information in column B.

Any Ideas
Thanks
Alec

Column Offset Update
 

Try this worksheet event code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address(0, 0) = "A1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If
End Sub

To install it, right click the tab at the bottom of the worksheet where you
want this functionality, select View Code from the popup menu and then
copy/paste the above code into the code window that appeared.

--
Rick (MVP - Excel)


"Alectrical" wrote in message
...
Hi
I have a cell with a DDE link from another program, which is sample data I
need to record.
To do this I need to copy the value of the cell, A1, and paste it into B1,
when the cell value changes I need to copy the new value from A1 into B2
and
so on. I can then produce a real time graph from the information in column
B.

Any Ideas
Thanks
Alec

Column Offset Update
 

Thanks Rick
That works a treat

"Alectrical" wrote:

Hi
I have a cell with a DDE link from another program, which is sample data I
need to record.
To do this I need to copy the value of the cell, A1, and paste it into B1,
when the cell value changes I need to copy the new value from A1 into B2 and
so on. I can then produce a real time graph from the information in column B.

Any Ideas
Thanks
Alec

Column Offset Update
 

Hi Rick

Just realised, the code only works if I change the value in A1 and then
press enter, It does not work with a link that updates every second.

"Rick Rothstein" wrote:

Try this worksheet event code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address(0, 0) = "A1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If
End Sub

To install it, right click the tab at the bottom of the worksheet where you
want this functionality, select View Code from the popup menu and then
copy/paste the above code into the code window that appeared.

--
Rick (MVP - Excel)


"Alectrical" wrote in message
...
Hi
I have a cell with a DDE link from another program, which is sample data I
need to record.
To do this I need to copy the value of the cell, A1, and paste it into B1,
when the cell value changes I need to copy the new value from A1 into B2
and
so on. I can then produce a real time graph from the information in column
B.

Any Ideas
Thanks
Alec