Need to pull data from the same cell in multiple workbooks.

  • Thread starter Thread starter Gluefoot
  • Start date Start date
G

Gluefoot

I have a file with about 200 Excel workbooks. Each workbook has 2 sheets. I
need to total up the values from cell "X12" on sheet2 for all 200 workbooks.
Each workbook has a different name. Is it possible to do this in a new excel
sheet?
 
Put this macro into a new workbook. Change the FOLDER as required (from
c:\temp)

Sub GetX12()

Folder = "c:\temp\"

Set SumSht = ActiveSheet
RowCount = 1

FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
SumSht.Range("A" & RowCount) = FName
SumSht.Range("B" & RowCount) = OldBk.Sheets("Sheet2").Range("X12")
RowCount = RowCount + 1
OldBk.Close savechanges:=False
FName = Dir()
Loop
End Sub
 
This worked perfectly. And it was my first experience creating a macro. Thank
you so much!
 
maybe you can help me out joel.

joel said:
Put this macro into a new workbook. Change the FOLDER as required (from
c:\temp)

Sub GetX12()

Folder = "c:\temp\"

Set SumSht = ActiveSheet
RowCount = 1

FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
SumSht.Range("A" & RowCount) = FName
SumSht.Range("B" & RowCount) = OldBk.Sheets("Sheet2").Range("X12")
RowCount = RowCount + 1
OldBk.Close savechanges:=False
FName = Dir()
Loop
End Sub
Click to expand...
 
This code works great but.....

This code works great for what I originally needed it for! I tried manipulating the code to allow me to pull many cells from each of the work books; but was lost in how to do that. For instance; I would like to pull a17:j33 from each of the workbooks. All of my attempts have only allowed me to capture 1 row from each of the workbooks. Can someone show how the above code would work with my changes incorporated please.
thanks in advance!
 
Back
Top