I’m not expert with vba codes and I have a workbook with 9 sheets have the same table design and I have got a vba code to copy the adjacent cell if the cell value equal any other cell in the range but it’s not working properly- my range is between columns “G:N” … in “G” I have “name” and in “H” I have the “phone number” and the same in the other coulmns as you can see in the screenshot

[

I need your help please to fix my code to to autofill the adjacent cell (phone number) if the “name” is repeated above in any cell of the range

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const START_ROW = 5
    Const COL_NAME_FIRST = 7 ' Col G
    Const CNT_NAME = 4
    With Target
        If .CountLarge > 1 Or .Row < START_ROW Or Len(.Cells(1).Value) = 0 Then Exit Sub
        Dim rngName As Range, i As Long, rngCol As Range
        Set rngName = Me.Columns(COL_NAME_FIRST)
        For i = 1 To CNT_NAME - 1
            Set rngName = Union(rngName, Me.Columns(COL_NAME_FIRST).Offset(, i * 2))
        Next
        If Application.Intersect(Target, rngName) Is Nothing Then Exit Sub
        Set rngName = Intersect(rngName, Me.UsedRange)
        For Each rngCol In rngName.Columns
            Dim vRes: vRes = Application.Match(.Value, rngCol, 0)
            If Not IsError(vRes) Then
                Application.EnableEvents = False
                .Offset(, 1) = rngCol.Offset(, 1).Cells(vRes)
                Application.EnableEvents = True
                Exit Sub
            End If
        Next
    End With
End Sub