Copy column data after last row on matching of heading

[gsgurpreetsingh501]

Problem is to copy the column data from one sheet to other sheet (after last row) on matching of the column heading.
In the below sheet, Name is matching in both the sheets. So matching on "Name" basis, the entire "Name" column in Sheet 1 has to be copied to the end of Sheet2 Name column.

Sheet1:

S.No	Date	weight	Name
1	4/3/2016	70	John
2	4/3/2016	75	Andrew
3	4/3/2016	67	Gill
4	4/4/2016	80	Macmillan

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet2:

S.No	Name	Days
1	Brian	2
2	Neil	5
3	Kim	10

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Result of Sheet2:

S.No	Name	Days	Duration (Days)
1	Brian	2	2
2	Neil	5	5
3	Kim	10	10
	John
	Andrew
	Gill
	Macmillan

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[gsgurpreetsingh501]

Please ignore the last column in "Result of Sheet2:", got added by mistake

 

[MARK858]

Possibly you can try something like the below code...

Sub FindIt()
    Dim myCol As Long, myconst As String

    myconst = Sheets("Sheet2").Range("B1").Value

    With Sheets("Sheet1")
        myCol = .Rows(1).Find(What:=myconst, After:=.Cells(1, .Columns.Count), LookIn:=xlValues, LookAt _
                        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
        .Range(.Cells(2, myCol), .Cells(.Cells(Rows.Count, myCol).End(xlUp).Row, myCol)).Copy _
                        Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    End With

End Sub

 

[gsgurpreetsingh501]

Thank you very much buddy. This is what i needed