ok i cobbled together this from above code
it just uses the text to columns and autofit. i couldnt get beyond18 sheets without running out of memory tho (with either code)
martin rice might have a better solution though
Code:
Sub Test()
Application.ScreenUpdating = False
Open "C:\Documents and Settings\Martin Wilson\Desktop\test[1].txt" For Input As #1
Do While Not EOF(1)
Counter = Counter + 1
Line Input #1, FileLine
ActiveSheet.Cells(Counter, 1) = FileLine
If Counter = 65000 Then
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Cells.Select
Cells.EntireColumn.AutoFit
Counter = 0
Sheets.Add
End If
Loop
Close #1
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Cells.Select
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub