how to merge two sheet in to master sheet?

[kim gutierrez]

i am working on this two sheets, the HR sheet and LOTUS NOTES sheet both are list of employees, in the HR sheet the names are complete (first name,last name, middle name) in one cell in lotus notes sheet the name are just lastname and firstname in a cell i need a master list where i will combine the list of employee in HR sheet and in LOTUS NOTES sheet but not doubled the entry name in master list (e.g.

HR Sheet

emp no.	emp name	email	status
1	glazyrene kim E. Gutierrez	gkeg@gmail.com	Active
2	Judith O. Espinosa	JOE@yahoo.com	Retired
3	Joan E. Ignacio	JEI@mail.com	Active

<TBODY>
</TBODY>

Lotus Notes sheet

emp no.	emp name	age	department
6	kim Gutierrez	25	Hr DEpartment
7	Jocelyn Ocampo	30	Accounting Dep
10	Judith Espinosa	39	It DEP

<TBODY>
</TBODY>

Master List

emp no.	emp name	age	department	Email	status
1	glazyrene kim E. Gutierrez	25	Hr DEpartment	gkeg@gmail.com	active
2	Judith O. Espinosa	39	It DEP	JOE@yahoo.com	retired
3	Joan E. Ignacio	32	Accounting Dep	JEI@mail.com	active
7	Jocelyn Ocampo	30	Accounting Dep	JO@BSP.com	active

<TBODY>
</TBODY>

i have this code where it creates master list in a workbook if it doesnt have and delete a master list if it has already so that the master list is updated. it does copy the data in both sheet like this


sheet1

emp no	emp name	status
1	glazyrene kim E. Gutierrez	active

<TBODY>
</TBODY>

sheet 2

emp no	emp name	status
2	glazyrene kim Gutierrez	active
3	Judith O. Espinosa	retired

<TBODY>
</TBODY>

master list

emp no	emp name	status
1	glazyrene kim E. Gutierrez	active
2	glazyrene kim Gutierrez	active
3	Judith O. Espinosa	retired

<TBODY>
</TBODY>

code:

Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Delete the sheet "MASTER LIST" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("MASTER LIST").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MASTER LIST"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "MASTER FILE"
'Fill in the start row
StartRow = 2
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets except the RDBMerge worksheet and the
'Information worksheet, you can ad more sheets to the array if you want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0)) Then
'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)
'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then
'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If
'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
End If
Next
ExitTheSub:
Application.Goto DestSh.Cells(1)
'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


" i appreciate any help thanks a lot and God bless"