Split Workbook

Ok I am having a serious problem and I need some help

What exactly I need help with is hard to explain but I will do my best.

I have an excel workbook file that I use as a tool to determine everybody’s annual pay increase. The workbook has three worksheets. The first worksheet is called "Data" the other two sheets are hidden but are called "Tables" and "Grades"

The data worksheet basically lists all of our supervisors that have hourly direct reports. There are input columns for the supervisors to rate their direct reports in certain areas. A score is generated from that input and that score determines what percent of market value the rate of pay should be. If the result is higher than the current rate of pay then the employee gets a raise. There are columns that perform this calculation that contain references to the hidden worksheets.

I have written few macros that appear to do what I need but the references are lost.

Ok now here is what I am trying to do exactly:

The first sheet in the workbook "data" has a listing of all the supervisors and their direct reports. I need to split this workbook up by supervisor so that I have a separate but identical workbook for each supervisor with only their reports listed.

This is how I have been doing it manually:

1-open the 2006NE.xls file

2-Select the row that contains the supervisor that I am making a separate file for

3-delete all other rows for the other supervisors

4-Protect the Data sheet-allow users to enter information into unlocked cells only

5-File---->Save As-----> LastName.FirstName.xls (of supervisor)

6-Close "LastName.FirstName.xls"

7-Reopen 2006NE.xls and select the next supervisor

8-repeat steps 2-7 until a separate file has been created for each supervisor

I have attached the file if looking at it helps at all, I had to cut it down a little because of it size. The real one has 1750 different supervisors, so you can see why I need to find a way to automate the above process, doing that for everybody would be far too tedious

Please help or if you have any suggestions

Erin