A
Alectrical
Hi
I have a link in A1 in sheet 1, pasted from A1 in sheet 2.
I need to record the data in column B, where when A1 value changes the new
value is then put in a new row in column B. The following code works only
when I Double click A1, then click on any other cell. It does not
automatically update column B as I would expect. I have sheet calculation set
to automatic and pressing F9 has no effect. Any ideas, I'm desperate to get
this working, I know it works because I had it working a few years ago, but I
cannot find the code.
Thanks in Advance
Alec
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address = "$A$1" 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
I have a link in A1 in sheet 1, pasted from A1 in sheet 2.
I need to record the data in column B, where when A1 value changes the new
value is then put in a new row in column B. The following code works only
when I Double click A1, then click on any other cell. It does not
automatically update column B as I would expect. I have sheet calculation set
to automatic and pressing F9 has no effect. Any ideas, I'm desperate to get
this working, I know it works because I had it working a few years ago, but I
cannot find the code.
Thanks in Advance
Alec
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address = "$A$1" 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