Drop Down Code Help

Question

I have the following code to enter a value based on a drop down.  It works well if the table is on the same sheet; however,  I need for this to work with the data in a different sheet named "Material"

[code]

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
selectedNa = Target.Value
If Target.Column = 4 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("Material_List"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub

[code]

Answer 1

Change ActiveSheet to Worksheets("Material")

Answer 2

Oh the first one is suppose to be column 4 the other should be 6. I am getting an error in the end sub

Answer 3

You have 2 sections of code that both apply to the same column 4 (column D) buy that look up a value in a different table.

How should that work?

Answer 4

Another Question . . . If I want multiple change events on same worksheet? I tried this, but does not work:

Private Sub Worksheet_Change(ByVal Target As Range)

'Updateby Extendoffice

selectedNa = Target.Value 

If Target.Column = 4 Then 

    selectedNum = Application.VLookup(selectedNa, Worksheets("Materials").Range("Material\_List"), 2, False) 

    If Not IsError(selectedNum) Then 

        Target.Value = selectedNum 

    End If 

        If Target.Column = 4 Then 

    selectedNum = Application.VLookup(selectedNa, Worksheets("Time").Range("Time\_List"), 2, False) 

    If Not IsError(selectedNum) Then 

        Target.Value = selectedNum 

    End If 

End If 

Exit Sub 

End Sub

Answer 5

I knew it was a simple fix. Thank you for the quick reply!