how to read multiple workbooks, collect a few cells from each?

  • Thread starter Thread starter ms
  • Start date Start date
M

ms

I get data in the form of 100 to 300 spreadsheets on a CD-ROM.
I only need a few numbers from each spreadsheet, let's say they
are in cells B4 and C7.
I would like a macro to read each workbook in a folder, and put
its B4 into column B and C7 into column C. i.e
B2 = B4 of 1st workbook
C2 = C7 of 1st workbook
B3 = B4 of 2nd workbook
C3 = C7 of 2nd workbook
B4 = B4 of 3rd workbook etc.

I know how to open muliple files in a folder, but not how to
populate a column, incrementing the row each time.
Where might I find a macro to do this?
 
psuedo code
dim rw as Row
Wkbk as Workbook
rw = 2
for each wkbk in ListofWorkbooks
' assume wkbk holds a reference to one of the
' workbooks which you have opened and this code
' is in the workbook where you want the summary

with Thisworkbook.worksheets("Sheet1")
.Cells(rw,"B").Value = wkbk.worksheets(1). _
Range("B4").value
.Cells(rw,"C").Value = wkbk.worksheets(1). _
Range("C7").value
rw = rw + 1
End With
 
Back
Top