Hi Rick Sorry to start a new thread, but just realised, the code only works
if I change the value in A1 and then
press enter, It does not work with a DDE 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)