Ponsonby,

here is the code. i have truncated some repetitive parts in the end as indicated.. I know this is not a very optimized way of writing the code, since i have no formal training with computers i just dont know how to write it efficiently.

Sub Macro1()

'

'

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Workbooks.OpenText Filename:="C:\temp\2004 acc\DATA\sms01000.txt", DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _

:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2))

Cells.Select

Selection.ColumnWidth = 8.57

Cells.EntireColumn.AutoFit

'opens the text file in excel comma delimited and i have set the format to be text because i dont want to change some date formats. the date format in the original file is yyyy-dd-mm. another advantage is that some numbers starting with 0 like 0765 are kept that way.

Application.Calculation = xlCalculationAutomatic

Range("Y1").Select

ActiveCell.FormulaR1C1 = "=WEEKDAY(RC[-23])"

Range("Y1").Select

Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))

Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 1).Address)

Range("AA1").Select

ActiveCell.FormulaR1C1 = "=YEAR(RC[-25])"

Range("AB1").Select

ActiveCell.FormulaR1C1 = "=MID(RC[-1],3,4)"

Range("Z1").Select

ActiveCell.FormulaR1C1 = "=R1C28&""-""&RC[-3]"

Range("z1").Select

Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))

Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 1).Address)

**' to add 04- before the ORC numbers**
Application.Calculation = xlCalculationManual

Columns("Z:Z").Select

Selection.Copy

Range("W1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Columns("W:W").EntireColumn.AutoFit

Columns("Z:AC").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Range("AA9").Select

Workbooks.OpenText Filename:="C:\temp\2004 acc\DATA\sms05000.txt", DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _

:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2))

Cells.Select

Selection.ColumnWidth = 8.57

Cells.EntireColumn.AutoFit

Range("N1").Select

ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-13],sms01000.txt!C1,sms01000.txt!C23)"

Application.Calculation = xlCalculationAutomatic

Range("N1").Select

Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))

Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 1).Address)

Columns("N:N").Select

Selection.Copy

Range("A1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Columns("N:N").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Cells.Select

Cells.EntireColumn.AutoFit

**'i replaced the above code as per ponsonby to read as,Range([N1], [M1].End(xlDown)(1, 2)).FormulaR1C1 = "=LOOKUP(RC[-13],sms01000.txt!C1,sms01000.txt!C23)"**

'Columns("A:A") = Columns("N:N").Value

'Columns("N:N").Delete

'Cells.Columns.AutoFit
Application.Calculation = xlCalculationManual

Workbooks.OpenText Filename:="C:\temp\2004 acc\DATA\sms06000.txt", DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _

:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2))

Cells.Select

Selection.ColumnWidth = 8.57

Cells.EntireColumn.AutoFit

Range("F1").Select

ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-5],sms01000.txt!C1,sms01000.txt!C23)"

Application.Calculation = xlCalculationAutomatic

Range("F1").Select

Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))

Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 1).Address)

Application.Calculation = xlCalculationManual

Columns("F:F").Select

Selection.Copy

Range("A1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Columns("F:F").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Cells.Select

Cells.EntireColumn.AutoFit

**'i replaced the above code as per ponsonby to read as,Range([F1], [G1].End(xlDown)(1, 2)).FormulaR1C1 = "=LOOKUP(RC[-5],sms01000.txt!C1,sms01000.txt!C23)"**

'Columns("A:A") = Columns("F:F").Value

'Columns("F:F").Delete

'Cells.Columns.AutoFit
Application.Calculation = xlCalculationManual

Workbooks.OpenText Filename:="C:\temp\2004 acc\DATA\sms08000.txt", DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _

:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2))

Cells.Select

Selection.ColumnWidth = 8.57

Cells.EntireColumn.AutoFit

Range("o1").Select

ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-14],sms01000.txt!C1,sms01000.txt!C23)"

Application.Calculation = xlCalculationAutomatic

Range("o1").Select

Set AutoFillRg = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown))

Selection.AutoFill Destination:=Range(ActiveCell.Address, AutoFillRg.Offset(0, 1).Address)

Application.Calculation = xlCalculationManual

Columns("o

").Select

Selection.Copy

Range("A1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Columns("o

").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Cells.Select

Cells.EntireColumn.AutoFit

'i

**replaced the above code as per ponsonby to read as,Range([O1], [P1].End(xlDown)(1, 2)).FormulaR1C1 = "=LOOKUP(RC[-14],sms01000.txt!C1,sms01000.txt!C23)"**

'Columns("A:A") = Columns("O:O).Value

'Columns("O:O").Delete

'Cells.Columns.AutoFit

'

' and so on.. the code is repitative for the rest of the files

'

'

'for 8 more files

'

'

'
Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

Thanks!