Excel 2007 VBA Macro help

[DontHateOnMe]

OK so I need a macro for this spreadsheet I am working on.

Here is the deal:

-I have a workbook with 4 different sheets in it. (1 hidden called names that has a predefined list... this is not in the sample provided)
-Each Sheet has a column in it with a name in Column A (CONNOR, JOHN)
-Each sheet should have the same people on it (some may be present on one but not the other and vice versa)
-The 1st sheet is a validation page
-Sheets 2-4 have headers that take up rows 1 thru 4

What I am trying to get a macro to do is to seperate each name in the workbook into its on seperate workbook with the 4 sheets(tabs).
http://www..experts-exchange.com/incoming/2011/05_w22/463261/sample.xls

The first sheet is a Validation page. It does not have the names on it. It pulls data for every row on the other 3 worksheets and uses a formula (IF formula) to see whether or not data has been validated. See example below. I will need this sheet exactly how it is (contains merged cells) as the first sheet on every workbook that gets created.

The 2nd and 4th sheets contain the data that I need seperated. Each of the sheets have names for each row in column A (starting at A5).

So for these sheets as an example lets say that it only has 2 names (mine has close to 250)

the 2 names are Connor, John and Bryant, Kobe

For sheet 2 each name has 3 rows(records) listed and each is different
Ex)
Connor,john | 123 | ABC
Connor,john | 456 | DEF
Bryant, kobe| x64 | P0B
Connor,john | 7rt | LK9
Bryant, kobe| irj | JHU
Bryant, Kobe| rust| HNM

and imagine sheet 3 and 4 are set up the same way.

Now what I am want this macro to do is to sepereate John, from Kobe and in this example 2 new workbooks would be created with the same sheets as the source (same sheet names) thus resulting in a connor file and a bryant file.

From the example above the connor file would look the same but it would only contain johns info and not kobe info. It would still have the first validation page though.

So after the macro is ran I would get 2 workbooks with 4 identical sheets in them and sheets 2-4 would only contain the rows with info where their name is in column A. The formulas used in the master sheet (where the macro is run) to check to see if data is valid or not should still work.

Please help me, I have been trying to do this for some time now and am having zero luck.

I have attached a sample file of what I am doing.
http://filedb.experts-exchange.com/incoming/2011/05_w22/463261/sample.xls

 

[DontHateOnMe]

I am a beginner with this Macro coding but below is what I have come up with so far. When I run this code it will run a filter on the name (the name is chosen from a defined list "Names" [a hidden worksheet]) but it will only create one sheet and it copies over all of the data, not just the filtered data. The problem is that It is basically just copying the existing workbook with filters applied.

Sub Copy()
Dim strSaveName As String

strSaveName = Worksheets("Names").Range("A1").Value
Worksheets("Sheet 2").Range("A5").AutoFilter Field:=1, Criteria1:=Worksheets("Names").Range("A1").Value
Worksheets("Sheet 3").Range("A5").AutoFilter Field:=1, Criteria1:=Worksheets("Names").Range("A1").Value
Worksheets("Sheet 4").Range("A5").AutoFilter Field:=1, Criteria1:=Worksheets("Names").Range("A1").Value
Sheets(Array("Validation Page", "Sheet 2", "Sheet 3", "Sheet 4")).Copy
ActiveWorkbook.SaveAs strSaveName
 
End Sub