gather criteria from multiple workbooks into 1 workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

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???
 
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!
 
Back
Top