How to copy a line in excel using a specific word and pasting to another excel sheet?

CODE

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("yourSheetName")
    
    strSearch = "Clarke, Matthew"
    
    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> I am assuming that the names are in Col A
        '~~> if not then change A below to whatever column letter
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
       
        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
        
        '~~> Remove any filters
        .AutoFilterMode = False
    End With
    
    '~~> Destination File
    Set wb2 = Application.Workbooks.Open("C:\Sample.xlsx")
    Set ws2 = wb2.Worksheets("Sheet1")
    
    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If
    
        copyFrom.Copy .Rows(lRow)
    End With
    
    wb2.Save
    wb2.Close
End Sub

SNAPSHOT

Expanding on what timrau said in his comment, you can use the AutoFilter function to find the row with your name in it. (Note that I'm assuming you have the source workbook open)

Dim curBook As Workbook
Dim targetBook As Workbook
Dim curSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Integer

Set curBook = ActiveWorkbook
Set curSheet = curBook.Worksheets("yourSheetName")

'change the Field number to the correct column
curSheet.Cells.AutoFilter Field:=1, Criteria1:="Clarke, Matthew" 

'The Offset is to remove the header row from the copy
curSheet.AutoFilter.Range.Offset(1).Copy  
curSheet.ShowAllData 

Set targetBook = Application.Workbooks.Open "PathTo Total Hours"
Set targetSheet = targetBook.WorkSheet("DestinationSheet")

lastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

targetSheet.Cells(lastRow + 1, 1).PasteSpecial

targetBook.Save
targetBook.Close 

As you can see I put placeholders in for the specific setup of your workbook.