Copying data and paste to other sheets with criteria

Hi guys! I would like to ask help about my problem in vba. I have workbooks , each workbook contains two sheets, the sheet1 only contains data about employees, his name, department, manager name, etc. and the other sheet which is sheet2 that contains data that I want to copy and paste to other workbook which will serve as my masterlist, in sheet1,sheet2,sheet3... the problem is I want to have a sheets that names after employee's last name and paste data into it, for example. Smith(name of employee) will be equal to Smith(name of the sheet) and the data within the Smith sheet will be the data of Smith..please someone help me about this..I already know the code for opening workbooks with they're corresponding paths.. Please please help, and thnaks in advance

Here's a sample thanks!

It works now! Amazing! I'll try to analyze your code! big thanks for the help man!

I just want to ask about this particular line of code .. Workbooks("" & mymacro & "") why include "" in this?

I Modified to suit my needs.. thanks again pal!

Hi Naveed still there? do you mind if I ask you question again?

my reference data