James,

This is the recorded macro and my macro put together:
-Paste this into a module and let me know if it works.

David Miller

==============================================

Sub FormatABunchOfFiles()
Dim myBook As Workbook
Dim sDir, sFile, sCurrent As String


With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With
sCurrent = ActiveWorkbook.Name
sDir = "YourDirectoryHere\"
sFile = "*.xls"


With Application.FileSearch
.NewSearch
.LookIn = sDir
.Filename = sFile
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute < 0 Then Exit Sub
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Sheets("Sheet1").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False,
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, 1), _
Array(6, 1)), TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1") = "Us#"
Range("B1") = "date"
Range("C1") = "ubd"
Range("D1") = "model"
Range("E1") = "serial"
Range("F1") = "quantity"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1") = "4 digit model"
Columns("E:E").EntireColumn.AutoFit
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Selection.AutoFill Destination:=Range("E2:E43")
Range("E2:E43").Select
Range("H1") = "combo"
Range("H2").Select
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-3],RC[-2])"
Selection.AutoFill Destination:=Range("H2:H43")
Columns("H:H").EntireColumn.AutoFit
myBook.Save
myBook.Close
Next i
End With
Windows(sCurrent).Activate
End Sub