Can I split a spreadsheet into multiple files based on a column in Excel 2007?

"As far as I know there is nothing short of a macro that going to split you data and automatically save it onto a set of files for you. VBA is probably easier.

Update
I implemented my suggestion. It loops through all the names defined in the named range 'RepList'. The named range is a dynamic named range of the form
=OFFSET(Names!$A$2,0,0,COUNTA(Names!$A:$A)-1,1)

module follows.

Option Explicit

'Split sales data into separate columns baed on the names defined in
'a Sales Rep List on the 'Names' sheet.
Sub SplitSalesData()
Dim wb As Workbook
Dim p As Range

Application.ScreenUpdating = False

For Each p In Sheets(""Names"").Range(""RepList"")
Workbooks.Add
Set wb = ActiveWorkbook
ThisWorkbook.Activate

WritePersonToWorkbook wb, p.Value

wb.SaveAs ThisWorkbook.Path & ""\salesdata_"" & p.Value
wb.Close
Next p
Application.ScreenUpdating = True
Set wb = Nothing
End Sub

'Writes all the sales data rows belonging to a Person
'to the first sheet in the named SalesWB.
Sub WritePersonToWorkbook(ByVal SalesWB As Workbook, _
ByVal Person As String)
Dim rw As Range
Dim personRows As Range 'Stores all of the rows found
'containing Person in column 1
For Each rw In UsedRange.Rows
If Person = rw.Cells(1, 1) Then
If personRows Is Nothing Then
Set personRows = rw
Else
Set personRows = Union(personRows, rw)
End If
End If
Next rw

personRows.Copy SalesWB.Sheets(1).Cells(1, 1)
Ser personRows = Nothing
End Sub

This workbook contains the code and the named range. The code is part of the 'Sales Data' sheet."

"If someone else answers with the correct way of doing this that is quick, please ignore this
answer.

I personally find myself using Excel and then spending a lot of time (somtimes hours) looking for a complicated way to do something or an over the top equation that will do everything when I will never use it again... and it turns out that if I just sat down and got on with the task manually it would take a fraction of the time.

If you only have a handful of people, what I recommend you do is simply highlight all the data, go to the data tab and click the sort button.

You can then choose what column to sort by, in your case you want to use Repname, then just copy and paste to individual files.

I am sure that using VBA or other tools, you may come up with a solution but the fact is, you will be looking at hours upon hours of work when just getting on with it by using the above method should get you done in next to no time.

Also, I think you can do this sort of thing on sharepoint + excel services, but that is a way over the top solution for this sort of thing."