"Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19). Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29). Sheet Unit-3 has only entries for unit number 30. Sheets for Unit-4 to Unit-9 are all blank."

this is exactly why i did i the way i posted, so this would not happen"
unitnum = Array("1 S", "2 S", "3 S", "4 S", "5 S", "6 S", "25 S")


--


Gary


"BobS" wrote in message .. .
Shane,

Your suggestion was a big help - things have morphed and I have a "bug" that will take more to explain than I'm prepared to do fully tonight. Kinda brain-dead, need a break. This is one of those that I can't see the forest for the tree's. Get data into all the sheets but only Unit-10 to Unit-30 sheets have all the proper data.

Unit-1 sheet to Unit-9 sheets have problems. In Unit-1 sheet, it has all the unit numbers that begin with a 1 (i.e. 10,11,12,13,14 to 19). Sheet Unit-2 has the same type entries except it's all starts with 2 (i.e. 20, and 21 to 29). Sheet Unit-3 has only entries for unit number 30. Sheets for Unit-4 to Unit-9 are all blank.

For Unit-10 to Unit-30 sheets - all have the correct data - not a single error.

Played with the filter (many times). I can find entries for all the unit numbers (01 to 30) in the Unit-1 sheet.

The log is a text file that I'm importing - no conversion of data from one format to another.

The log shows data entries as below except they show better in Wordpad and are nicely tab delimited: (downsized so it will not break - I hope).

A B C D E F
-----Date/Time-------------------|------EquipDesc-----------------------------|--------Data 1----------------|Data 2|Data3|Data4|

4/26/2008 12:00:10 AM Route 3 RTMS 30 Southbound 03(High_Speed_SB_3) 67 2367 1.0
4/26/2008 12:00:10 AM Route 3 RTMS 03 Northbound 01(Low_Speed_NB_1) 67 4118 2.0

4/26/2008 12:00:10 AM Route 3 RTMS 03 Northbound 02(Middle_Speed_NB_2) 58 7696 3.0

4/26/2008 12:00:10 AM Route 3 RTMS 03 Northbound 03(High_Speed_NB_3) 84 4636 2.0

4/26/2008 12:00:10 AM Route 3 RTMS 25 Southbound 01(Low_Speed_SB_1) 66 4115 3.0

4/26/2008 12:00:10 AM Route 3 RTMS 01 Southbound 02(Middle_Speed_SB_2) 58 6659 4.0

4/26/2008 12:00:10 AM Route 3 RTMS 05 Southbound 03(High_Speed_SB_3) 65 3244 2.0

So the EquipDesc field is filtered using the following code:

T is Dim'd As Byte
UnitNum = 0

For T = 1 To 30
UnitNum = UnitNum + 1
[A5].CurrentRegion.AutoFilter Field:=2, Criteria1:="Route 3 RTMS " & T & "*"
Range([A5], Range("F" & [A5].SpecialCells(xlLastCell).Row)).SpecialCells(xlCel lTypeVisible).Copy _
Sheets("Unit-" & T).Range("A" & Sheets("Unit-" & UnitNum).[A5].SpecialCells(xlLastCell).Row + 1)
Next T

The UnitNum is used for selecting the sheet number instead of using T. I'm thinking there is some sort of type mis-match here that I'm not seeing and it doesn't cause an error. From what I'm seeing it looks like the filter can't filter on a number with a leading zero - or it discards it or.....?

I did make up a another macro that sorted the data as it's being imported using Line Input and Select Case statements. Worked great, everything went on it's respective sheet until I hit the 64K limit of records. Even though I had far fewer than 64K rows on each sheet - that 64K limit also limits you on importing records as well as the max number of rows per sheet.

So I went back to the macro that gets all the data in and places it on 3 worksheets - a total of over 185,000 records. Then I use the Autofilter on each of those 3 data sheets to filter/sort and copy the entries to their respective Unit numbered sheet. Everthing works except sheets 1 to 9 as I explained above.

Scratchin head 'n butt on this one.....

Thanks for your help,

Bob S.