If name is the same between two sheets, have certain columns populate data

[steinhk6]

Ok new to programming VBA first off.

My problem is I have two sheets they both contain a long series of names, some of the names between the two sheets are the same.

For each name there are four columns containing data that I filled out.

Im wanting names that match between the two sheets to have that data in the four columns auto populate

Names are in Column A
Data in columns E through H

thanks in advance for the help.

[steinhk6]

Help please, Im sure this is easy for someone.

[PushCar]

If you change last row in cell A in Sheet1 the macro find in the Sheet2 the same name and copy the four values from Sheet2 columns E-H to Sheet1 to columns E - H.

Code for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Integer
LRow = Range("A" & Rows.Count).End(xlUp).Row

 If Target.Address = Range("A" & LRow).Address Then
    Call FindNameCopyValues
End If
End Sub

and code for Module:

Sub FindNameCopyValues()

    Dim LastRow As Integer
    Dim MyValue As Variant
    Dim rng As Range
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    MyValue = Range("A" & LastRow).Value
      
    With Sheets("Sheet2").Range("A:A")
    
        Set rng = .Find(What:=MyValue, _
                  After:=.Cells(.Cells.Count), _
                  LookIn:=xlFormulas, _
                  LookAt:=xlWhole, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlNext, _
                  MatchCase:=False)
                    If Not rng Is Nothing Then
                       rng.Offset(, 4).Resize(, 4).Copy Range("E" & LastRow, "H" & LastRow)
                    End If
    End With
End Sub

Here is the file:
data.xls

[steinhk6]

Thanks that is a start, but I need it to not just check the last row... For instance if 20 names are pasted into sheet two I need them to all auto populate the four columns if they were in the previous sheet, sheet1...

Thanks again for the help.

[PushCar]

If you only pasted into sheet two, this is for you:

Code for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Columns("A")) Is Nothing Then
  
    Dim LRow As Integer
    Dim LRows As Integer
    Dim MyArray As Variant
    Dim MyValue As Variant
    Dim ValCount As Integer
    Dim AdrVal As String
        
    LRows = Range("A" & Rows.Count).End(xlUp).Row
    LRow = Range("E" & Rows.Count).End(xlUp).Row
    MyArray = Selection
    ValCount = Selection.Rows.Count
    AdrVal = Selection.Columns.Address
        
        With Sheets("Sheet1").Range("A:A")

            For i = 1 To ValCount

                If ValCount > 1 Then
                    MyValue = MyArray(i, 1)
                Else
                    MyValue = MyArray
                End If
    
                    Set rng = .Find(What:=MyValue, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
                        
                        If Not rng Is Nothing Then
                        
                        On Error Resume Next
                        rng.Offset(, 4).Resize(, 4).Copy Range(AdrVal).Rows(i).Offset(, 4).Resize(, 4)
                        On Error GoTo 0
                    
                        End If
                        
            Next i
    
        End With

    End If
End Sub

Here is the file:
data.xls

[steinhk6]

Hmmm, that isn't working right...

The data from column A is moving into column B.

I still need the data from E-H Columns to auto populate for any name from A column that matched from sheet1 to sheet2.

Thanks again