Code: Select all
Sub insertAnnotation()
Range("L6").Formula = "=" & Range("namedRange").Text
End Sub
If I move the one liner to the code for the sheet and put it into Worksheet_Change and add some bells and whistles to trigger the insertion of the formula when another cell is changed:
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim triggerCell As Range
Set triggerCell = Range("L4")
If Not Application.Intersect(triggerCell, Range(Target.Address)) _
Is Nothing Then
Range("L6").Formula = "=" & Range("namedRange").Text
End If
End Sub
Run-time error '1004':
method 'Range' of object'_Worksheet' failed
which I take to mean it can't interpret 'namedRange' as a range because, even though it s a global named range, 'namedRange' is a cell on another sheet in the book. I don't want to move the named range off the sheet where it is.
So how do I workaround this?
Ken