gather criteria from multiple workbooks into 1 workbook

[Guest]

i am trying to get information from multiple workbooks and have this info all together in one workbook. the info i am trying to get is in the same cells in each of the seperate workbooks. Cells- A2 D2 H20 and H22
A2= job name
D2= job #
H20= actual sq. ft.
H22= est. sq. ft.
and if possible when a new workbook is added to the folder where they are all saved this info will automatically filled in???

 

[keepITcool]

As they say.. Several Roads may lead to Rome...

This one comes from Amsterdam


Following creates the list..
with linked formulas..and converts formulas to values.


Sub keepITcool4Brian()
Dim col, itm, res, i%, sPath$, sLink$
Cells.Clear
'set the folder to browse...
sPath = VBA.CurDir

Set col = CreateObject("Scripting.Filesystemobject").GetFolder
(sPath).Files
ReDim res(1 To col.Count)
For Each itm In col
If LCase$(itm) Like "*.xls" And itm.Name <> ThisWorkbook.Name Then
i = i + 1
With Cells(i, 1)
'edit the sheet name...
sLink = "='" & sPath & "\[" & itm.ShortName & "]sheet1'!"
.Offset(0, 0) = itm.ShortName
.Offset(0, 1) = sLink & "$a$2"
.Offset(0, 2) = sLink & "$d$2"
.Offset(0, 3) = sLink & "$h$20"
.Offset(0, 4) = sLink & "$h$22"
'delete following if you want to keep the formulas
.Resize(, 5).Value = .Resize(, 5).Value
End With
End If
Next

End Sub


Cheerz!