Sub ABC()
Dim sPath As String, sName As String
Dim bk As Workbook ', sh As Worksheet
Dim wshLoop As Worksheet
Dim rw As Long
Set sh = ActiveSheet 'I will record the value and workbook name
' in the activesheet when the macro runs
rw = 3 ' which row to write to in the activesheet
sPath = "C:\Users\Heinrich Venter\Documents\Trend files\"
sName = Dir(sPath & "*trend*.xls") ' for xl2007 & "*.xls"
Do While sName <> ""
Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3") ' Name
sh.Cells(rw, "C") = wshLoop.Range("N14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7") ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9") ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("P10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("M16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("M17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("M18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("M19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("M20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("M21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("M22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("M23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("M24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("M25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("M27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("M29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G41") ' Comment Ser 1
sh.Cells(rw, "T") = wshLoop.Range("F41") ' Check field
rw = rw + 1
Next wshLoop
Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = bk.Worksheets(1).Range("I3") ' Name
sh.Cells(rw, "C") = bk.Worksheets(1).Range("N14") ' Date
sh.Cells(rw, "D") = bk.Worksheets(1).Range("J7") ' Client
sh.Cells(rw, "E") = bk.Worksheets(1).Range("Q9") ' Circuit
sh.Cells(rw, "F") = bk.Worksheets(1).Range("P10") ' Volume m3
sh.Cells(rw, "G") = bk.Worksheets(1).Range("N16") ' SS
sh.Cells(rw, "H") = bk.Worksheets(1).Range("N17") ' Con
sh.Cells(rw, "I") = bk.Worksheets(1).Range("N18") ' pH
sh.Cells(rw, "J") = bk.Worksheets(1).Range("N19") ' Soluble Iron
sh.Cells(rw, "K") = bk.Worksheets(1).Range("N20") ' Total Iron
sh.Cells(rw, "L") = bk.Worksheets(1).Range("N21") ' Mol
sh.Cells(rw, "M") = bk.Worksheets(1).Range("N22") ' Nitrite
sh.Cells(rw, "N") = bk.Worksheets(1).Range("N23") ' Glycol
sh.Cells(rw, "O") = bk.Worksheets(1).Range("N24") ' Aerobic bacteria
sh.Cells(rw, "P") = bk.Worksheets(1).Range("N25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = bk.Worksheets(1).Range("N27") ' Mild steel
sh.Cells(rw, "R") = bk.Worksheets(1).Range("N29") ' Copper
sh.Cells(rw, "S") = bk.Worksheets(1).Range("G42") ' Comment Ser 1
sh.Cells(rw, "T") = bk.Worksheets(1).Range("F42") ' Check field
rw = rw + 1
Next wshLoop
bk.Close SaveChanges:=False
sName = Dir()
Loop
End Sub
Thank you.
Bookmarks