VBA CODE HELP

[dlafko1]

Hello,
I have this code (which someone provided)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/3/2020  8:58:13 AM  EST
If Target.Column = 2 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Variant
ans = Target.Value
Dim dteRow As Variant
Dim x As Variant
With Sheets("JobCode_Name")
    dteRow = Application.Match(ans, .Columns(1), 0)
    
      If IsNumeric(dteRow) Then
            x = .Cells(dteRow, 2).Value
            Target.Offset(, 1).Value = x
    Else
        MsgBox ans & vbNewLine & "Notfound"
    End If
End With
End If
End Sub

It takes a number in Columb 2 of my sheet and then puts in other data in Columb 3 I have the data on a separate sheet.

I would like to do the same thing in another columb.
I want to in Columb 6 add text which then puts a number in Columb 5 the data is stored on another sheet called Agency Code.

I tried this but it did not work

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/3/2020  8:58:13 AM  EST
If Target.Column = 6 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Variant
ans = Target.Value
Dim dteRow As Variant
Dim x As Variant
With Sheets("AgencyCode_Name")
    dteRow = Application.Match(ans, .Columns(5), 0)
    
      If IsNumeric(dteRow) Then
            x = .Cells(dteRow, 2).Value
            Target.Offset(, 1).Value = x
    Else
        MsgBox ans & vbNewLine & "Notfound"
    End If
End With
End If
End Sub

Any Guideance would be great. I am not good with VBA and this was the first time I put code in an excel sheet. The data is stored on the sheet called Internal.

Thanks for the help.

 

[n1lesh]

If the sheet is called Internal change
With Sheets("AgencyCode_Name") to
With Sheets("Internal")

 

[dlafko1]

If the sheet is called Internal change
With Sheets("AgencyCode_Name") to
With Sheets("Internal")

Sorry the Sheet that has the Data I want to choose is from AgencyCode
The sheet the data will be put in to is internal.

So the code I was trying to change takes information from a sheet call JobCodes and then stores it in the Internal Sheet.. I want to do the same thing with another set of data in different columns