Rob, I am getting an error message and I am pretty sure
that it is caused by the fact that the data is imported
from a text file and the workbook is named "test.txt". We
get a run-time error "1004:"
Method 'Name' of object'_Worksheet' failed

When we debug, the highlighted code is "wks.Name=rng.Cells
(1).Value"

If you are still "listening" here, do you have a solution?

-----Original Message-----
Ken,

Sub test()
Dim i As Long, lngLastRow As Long, rng As Range

With Sheet1
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lngLastRow
If IsEmpty(.Cells(i, 2).Value) Then
If Not rng Is Nothing Then CopyRangeToWKS
rng
Set rng = .Rows(i)
Else
Set rng = Union(rng, .Rows(i))
End If
Next
If Not rng Is Nothing Then CopyRangeToWKS rng
End With
End Sub

Sub CopyRangeToWKS(rng As Range)
Dim wks As Worksheet

Set wks = Worksheets.Add(After:=Worksheets
(Worksheets.Count))
wks.Name = rng.Cells(1).Value
wks.Cells(1, 1).Value = "Name"
wks.Cells(1, 2).Value = "ID"
wks.Cells(1, 3).Value = "Amt"
rng.Copy wks.Cells(2, 1)
End Sub


Rob


"Ken" wrote in
message
...
(We put this out to the "Worksheet Functions" group
yesterday and have had no response. Either we explained
it
too poorly to get a response; it is too much programming
assistance to ask from this source; or the folks in that
group did not have the macro expertise to respond. So,
we
are trying for assistance here.)

Using Excel 2000.

We have 2,000 - 3,000 rows of imported text data.

Sample layout is:
A B C D

1 ABC
2 xx xx $45
3 xx xx $34
4 xx $4
5 FGE
6 xx xx $55
7 xx $67
.....

Rows are sorted based on the entries in Column A if
there
is a blank entry in column B. There are about ten
groupings of sorted items - ten groups
labeled "ABC", "FGE", etc.
Each new group starts with a blank entry in column B.
We are trying to get a macro that will loop through the
3,000 rows; copy the range of rows from one group
(e.g. for group "ABC" we would copy rows one through
four); insert a new worksheet; paste the copied
rows to the new worksheet; rename the new worksheet with
the label from column A (e.g. "ABC"); insert a new row 1
into the new worksheet with A1="Name", A2="ID",
A3="Amt";
and then loop back.
Then the macro will have to stop when it realizes it is
at
the end of the data. (Alternatively, the macro could
start
at the bottom and work back to the top.)
TIA.


.