I quickluy modified a similar request I did earlier in the week. This code
will seperate the rows a put each group in its own worksheet. You need to
use two variables. A rowCount and a Start (the 1st row fo the group).

Sub splitcustomers()

Set SumSht = Sheets("Summary")
With SumSht
LastRow = Range("B" & Rows.Count).End(xlUp).Row
RowCount = 2
StartRow = RowCount
Do While RowCount <= LastRow
'check to see where one customer ends
If .Range("A" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'Create New worksheet at end of workbook
Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
'rename new worksheet
customer = .Range("A" & StartRow)
newsht.Name = customer

'Copy head row to new sheet
.Rows(1).Copy Destination:=newsht.Rows(1)
'Copy customers to new sheet
Set CopyRange = .Rows(StartRow & ":" & RowCount)
CopyRange.Copy Destination:=newsht.Rows(2)
StartRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With


"JOSEPH WEBER" wrote:

I have a spreadsheet I am creating from a query ran in another program. I
have gotten pretty far in the macro programming as far as what i would like
to do. There is one part i can't figure out. I have the macro set up so it
looks for a value in sheet two to tell it which lines to copy, but i cant
figure out how to tell Excel to do it. for example, the data in column a is
a name, then all other lines in column a are blank for that particular
person. Once the macro comes across another name, i want it to select the
previous line all the way back up to the name, so i can get that whole
section. I have a counter set up so i can tell it how many lines to copy,
but just can't figure out how to tell Excel to do that.