see if something like this may work. i have no idea what the name of your sheets
are.
notice the unitnum array, you need to complete the list for all possibilities.
i only have 1 - 6 and 25. the S was added to distinguish between 2 and 25 and
every other possibility.
the actual copy to sheet 2 is commented out because i don't know the name of
your destination sheets.
but step through this code and see if the autofilter actually filters by each
unit number. if it works, you should see the copy selection around the range to
be copied.



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
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
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 25*",
Operator:=xlAnd
.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
Set rng = ws.Range(ws.Cells(fRow, "D"), _
ws.Cells(Rows.Count, _
"D").End(xlUp)).SpecialCells(xlCellTypeVisible )
ws.Range("A" & fRow, "F" & lRow).Copy 'ws2.Range("A" &
y)
y = y + 1
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.