Gary,

As soon as I finish my morning "wake'em up", I'll give this a try. I
appreciate your efforts and the example code to get me going.

Thank you,

Bob S.



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i did some more testing. i added sheets named unit 5 and unit 25. i then
replicated "Route 1 Unit 25 Southbound" down column b on sheet1 and changed
some to Route 1 Unit 5 Southbound.

this code seemed to put them on the correct sheet, but like i mentioned, i
don't know your exact layout.



Sub test()
Dim unitnum As Variant
Dim rng As Range
Dim z As Long, y As Long
Dim lastrow As Long
Dim ws As Worksheet, ws2 As Worksheet
Dim fRow As Long
Dim lRow As Long
Dim num As Variant
Set ws = Worksheets("Sheet1")
lastrow = Application.Min(ws.Cells(Rows.Count, "B").End(xlUp).Row,
65535)
unitnum = Array("1 S", "2 S", "3 S", "4 S", "5 S", "6 S", "25 S")
Range("A1:f1") = Array("Date/Time", "EquipDesc", "Data-1", "Data-2",
_
"Data-3", "Data-4")
y = 2
For z = LBound(unitnum) To UBound(unitnum)
With ws.Range("A1:f" & lastrow)
.AutoFilter Field:=2, Criteria1:="=*unit " & unitnum(z) &
"*"
fRow = .CurrentRegion.Offset(1, _
0).SpecialCells(xlCellTypeVisible).Row
lRow = .Cells(Rows.Count, "B").End(xlUp).Row

If
.Columns(1).SpecialCells(xlCellTypeVisible).Cells. Count < _
1 Then
num = Split(unitnum(z))
Set ws2 = Worksheets("unit " & num(0))
ws.Range("A" & fRow, "F" & lRow).Copy ws2.Range("A"
& y)
End If
End With
Application.CutCopyMode = False
Next
ws.AutoFilterMode = False
End Sub
--


Gary


"BobS" wrote in message
...
Novice
Excel 2003

Have a large text file (equipment log data) that I import using a macro
created by Chip Pearson (www.cpearson.com) called "ImportBigTextFile"
that I have modified slightly and it works fine.

The text files I'm working with have approx 180,000+ lines of data and
the above macro allows me to import data for 65,536 rows per sheet
without having to break up the original log file.

The imported data is tab-delimited and creates 6 columns of data (A to F)
with a header row:

"Date/Time" "EquipDesc" "Data-1" "Data-2" "Data-3" "Data-4"

Col B (EquipDesc) which has the equipment description is a string of data
similar to this:

"Route 1 Unit 25 Southbound"

I want to be able to find the equipment Unit number (1 - 30 which are
always characters 14 & 15 in the string) in each of the rows in col B on
all the sheets and then copy and paste the entire row into a different
sheet specifically for that Unit number (30 worksheets).


For 180,000+ rows of data, that means I have 3 sheets that already have
65,536 rows of imported data that are sorted by date/time, top-to bottom.
What I need is to find all entries for each Unit number (1-30) on each
sheet then copy those entries into it's own sheet. An example of how to
find text characters within a string would be most helpful.

The workbook already has the worksheets (Unit-1, Unit-2,.....Unit-30) set
up so as I sort down thru the rows of the 3 sheets of the imported data,
I would then do a Case Select on it and then paste that row of data onto
it's corresponding sheet (Unit-1 to Unit-30). I would include counters
in the Case Select statements to increment the row count for each sheet
as each entry is made.

Ideally, sorting the data during input and copying it to it's own
worksheet would be a better method probably but I can't figure out how to
do that. Anyone know of an example that would show that? Since there are
30 Units, the max row entries per worksheet would be around 6,000 rows
(times 30 worksheets) and I would not need the above routine for
importing more than 64K rows.

Thanks for your time, comments and suggestions.

Bob S.