here is the code:
Sub module3()

Dim m As Integer
Dim value As Integer
Dim i, j, n As Integer
Dim k As String
Dim DaSourceFile As String, DaSourceAfter As String

DaSourceAfter = "D:\Excel\" 'fix the folder direction

DaSourceFile = Dir(DaSourceAfter & "*.xls") 'get the file *.xls

'loop through all of the txt files

Do While DaSourceFile < ""
Workbooks.Open Filename:=DaSourceAfter & DaSourceFile 'open one file

Worksheets.Add
Worksheets(1).Activate
j = 2 'the fisrt row is used for note
Worksheets(1).Cells(1, 1) = "lane1"
Worksheets(1).Cells(1, 2) = "lane2"
Worksheets(1).Cells(1, 3) = "lane3"
Worksheets(1).Cells(1, 4) = "total volume"
n = 1 'fix the first column as the volume of different lane
For i = 1 To 700 'row search the volume
Worksheets(2).Activate ' this sheet contains the data from cosim
If Worksheets(2).Cells(i, 9) = "RADAR" Then 'Search condition for row
value = Worksheets(2).Cells(i, 10).value ' the data needed in th
project
Worksheets(1).Activate ' this sheet contains the data I need
Worksheets(1).Cells(j, n) = value 'give the value of sheet1 to sheet2
n = n + 1
If n = 4 Then 'sum the volume in different lanes
Worksheets(1).Cells(j, 4) = Worksheets(1).Cells(j, 1) + _
Worksheets(1).Cells(j, 2) + Worksheets(1).Cells(j, 3)
j = j + 1
n = 1
End If
Worksheets(2).Activate
End If

Next i

'Application.DisplayAlerts = False

ActiveWorkbook.Save

ActiveWorkbook.Close SaveChanges:=True

DaSourceFile = Dir()

Loop
End Su

--
Message posted from http://www.ExcelForum.com