What I would do is:

1) resort the original file by sales person (column G)
2) work down column G, each time the entry in column G changes, cop
that range of rows to a new file
3) sort the original file back the way it was in the beginning

Something like this:

Sub ExportBySalesPerson()

LastRow = Range("A1").End(xlDown).Row
Range("A1:G" & LastRow).Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal

SalesRep = Range("G2")
FirstRow = 2
For myRow = 2 To LastRow + 1
If Cells(myRow, "G") < SalesRep Then
Workbooks.Add Name:=SalesRep
ThisWorkbook.Activate
Rows("1:1").Cop
Destination:=Workbooks(SalesRep).Range("A1")
Range(Cells(FirstRow, "A"), Cells(myRow - 1, "G")).Copy _
Destination = Workbooks(SalesRep).Range("A2")
Application.CutCopyMode = False
Workbooks(SalesRep).Save
Workbooks(SalesRep).Close
End If
Next myRow

End Su

--
Message posted from http://www.ExcelForum.com