VBA code to find contains text from one sheet to other

Hello,

Can anyone help me on this?.. Here is my requirement

I have Name column in sheet 1 and sheet 2.. i Need to find each name from sheet 2 to sheet 1 name range. if any of the few letters matches in both the sheets in Name column i need to copy both the sheet rows into Sheet 3 and name it in last column for respective row from which sheet it has come (whether that matching line is from Sheet1 or sheet 2).

Can someone help me on this....

Thank you in advance