worksheet_change can reference excel range on other worksheet

D

Dreiding

My problem is worksheet code "worksheet_change" does not allow usage of a
named range thatis in another worksheet.
Example code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant

If Target.Address = Range("A1").Address Then
Range("A4").Value = Range("myDate").Offset(Range("A1").Value - 1,
0).Resize(1, 1).Value
End If

End Sub

This code is behind worksheet1, the range "myDate" is in worksheet2

Any suggestions?
 
J

JLGWhiz

See if it works when you tell it where to find the range:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Start As Variant
If Target.Address = Range("A1").Address Then
'Tell it where Range("myDate") is
Range("A4").Value = Worksheets(2).Range("myDate").Offset(Range _
("A1").Value - 1, 0).Resize(1, 1).Value
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top