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

Your Amazing. Thank you!

Answer 3

This is what I have, but not working

Private Sub Worksheet_Change(ByVal Target As Range)

'Updateby Extendoffice

selectedNa = Target.Value 

If Target.Column = 5 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 = 7 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 4

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim selectedNa As Variant
    Dim selectedNum As Variant
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo ExitHere
    Select Case Target.Column
        Case 4
            selectedNa = Target.Value
            selectedNum = Application.VLookup(selectedNa, Worksheets("Materials").Range("Material_List"), 2, False)
            If Not IsError(selectedNum) Then
                Target.Value = selectedNum
            End If
        Case 6
            selectedNa = Target.Value
            selectedNum = Application.VLookup(selectedNa, Worksheets("Time").Range("Time_List"), 2, False)
            If Not IsError(selectedNum) Then
                Target.Value = selectedNum
            End If
    End Select
ExitHere:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub