How to search for a keyword and return the ENTIRE cell contents to a new sheet?

Formula

=VLOOKUP("*" &A5&"*",Sheet2!B:B,1,0)

VBA you could use

Sub Oh_Yah()
    Dim sh As Worksheet
    Dim ws As Worksheet
    Dim Shrng As Range, c As Range, shRw As Long
    Dim Wsrng As Range, w As Range, wsRw As Long
    Set sh = Worksheets("Sheet2")
    Set ws = Worksheets("Sheet1")
    With ws
        wsRw = .Cells(Rows.Count, "A").End(xlUp).Row
        Set Wsrng = .Range(.Cells(5, 1), .Cells(wsRw, 1))
    End With
    With sh
        shRw = .Cells(Rows.Count, "B").End(xlUp).Row
        Set Shrng = .Range(.Cells(1, 2), .Cells(shRw, 2)).SpecialCells(xlCellTypeConstants, 23)
    End With
    For Each c In Wsrng.Cells
        Set w = Shrng.Find(what:=c, lookat:=xlPart)
        If Not w Is Nothing Then
            c.Offset(, 1) = w
        Else: MsgBox "Not Found"
        End If
    Next c
End Sub