Search closest matching row from a UserForm entry, copy match, paste range to ActiveRow

Hi there

I have been searching, trialling (and failing) for a solution to my problem for three weeks now, but I can’t seem to solve it as I’m a complete VBA beginner! But I’m loving learning new things as I go.

I am trying to find a way to search for the most recent matching row entry (in the same sheet) when a new text value is entered via a UserForm on column A. Then, copy cells B:E into the active row.

To explain my goal step-by-step:

1. When a blank cell in column A is clicked, the UserForm named NewEntry is called.
2. When a user selects an existing name from the ListBox and clicks submit on the UserForm, it populates the active cell with this selection. Alternatively, the user can freetext a new name entry into the TextBox and click submit to populate the active cell.
3. What I want to do next (and I can’t figure this out) is:
4. When a new entry is submitted, search up the spreadsheet to find the most recent (i.e. the highest row number) name match in column A.
5. If a match is found, copy this matching row data from column B to column E (i.e. not the entire row)
6. Then, paste this matching data into the same range in the active row. The intent is to pre-fill information for users to avoid excess data entry.

I’ve been fumbling around with the following code placed in the UserForm itself:

Sub FindAndCopyAdjacent()
Dim Cell As Range
Set Cell = Range(ActiveCell).Find(What:=TextName.Text & ListName.Text)
If Not Cell Is Nothing Then
Range(Cell, Cell.Offset(0, 1)).Copy Destination:=("B:H")
'need more info to determine destination range
End If
End Sub

But I’m sure this is way off track.

I would be so incredibly grateful if someone could help me.
I have attached a sample workbook.