Creating multisheet workbook from multiple templates, vba

[shanzek]

I have a worksheet with a list of employees and job codes. I also have a template for the first half of a review that is common to all employees. The second half of the review varies based upon the job code. I have templates for all job codes, using the job code as the name. What I need to do is loop through the list of employees and do the following:
(1) Create a new worksheet named "employee_name" & "_Review.xls"
(2) copy the first template into the new worksheet as sheet1
(3) find the appropriate job code template and copy in the first sheet of that template into the new worksheet
(4) Save the new worksheet as the "employee_name" & "_Review.xls"
(5) loop back and get another employee...

I've got steps 1 and possibly 4 and 5, but could use some pointers / sample code for 2 and 3.

Here's what I have so far... please don't laugh...

' this is list of emp#, emp name, job code, job desc
Sheets("Department Employees").Select  

' figure max 100 employees per department - could make it larger if needed...
Range("a10:d109").Select    

Do Until ActiveCell = ""

    Emp_no = ActiveCell.Range("a1").Text
    Emp_Name = ActiveCell.Range("b1").Text
    job_code = ActiveCell.Range("c1").Text
    Job_Desc = ActiveCell.Range("d1").Text
    
    SaveAsName = Emp_Name & "_Review.xls"

' replace comma space with underscore...
' name in last, first format...

    SaveAsName = Replace(SaveAsName, ", ", "_", 1)


' create new workbook...
' this is the template for part 1 of the review...
Set NewBook = Workbooks.Add("s:\steve\NHP_Reviews\vbb_default.xlt")

Worksheets("Sheet1").Select

'rename sheet 1 tab with employee name and vbb review...
Worksheets("Sheet1").Name = Emp_Name & "_VBB Review"

' check to see if name used?
existfile = Dir(SaveAsName)

'save with name
ActiveWorkbook.SaveAs Filename:=SaveAsName

' but at this point it only has first half of review...
' need to open the template with job code...

'close new employee specific workbook..

NewBook.Close saveChanges:=False

'move down a row...
    ActiveCell.Offset(1, 0).Range("a1:d1").Select
    
Loop

Thanks
Steve