Copy part of a cell to 2nd workbook

Try the InStrRev fn:

Sub test()
    Dim LastName As String
    LastName = Range("C45").Value
    Range("E2").Value = Right(LastName, Len(LastName) - InStrRev(LastName, " ", -1))
End Sub