How to Create Macro to Search, Copy, & Paste?

Try this:

Sub foo()
    Dim lRow As Long, lEndRow As Long, lPasteRow As Long, lPasteCol As Long
    Dim wsSrc As Worksheet, wsTgt As Worksheet, c As Range, v As Variant

    Set wsSrc = Worksheets("Sheet2")
    Set wsTgt = Worksheets("Sheet1")

    lPasteRow = wsTgt.Cells(Rows.Count, 1).End(xlUp).Row
    With wsSrc
        lEndRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For lRow = 1 To lEndRow
            If .Cells(lRow, 1).Value = "Name" Then lPasteRow = lPasteRow + 1
            v = .Cells(lRow, 1).Value
            Set c = wsTgt.Range("1:1").Find(v)
            If Not c Is Nothing And Not v = "" Then
                wsTgt.Cells(lPasteRow, c.Column).Value = .Cells(lRow + 1, 1).Value
            End If
        Next lRow
    End With
End Sub