Extracting specific Cells

[Butters]

I have about 40 workbooks that are all uniform in structure and size. I want to write a macro that will extract data from certain cells (the same ones in each workbook) from a certain tab (named the same in each workbook) and spit them into a new workbook. Any ideas?

Thanks

Josh

 

[ThomasB]

Hi Josh

Have a look at this link and adapt the vba code, if you are unable to do so get back to me

Rgs

Thomas

http://www.mrexcel.com/forum/showthread.php?t=37126

 

[ThomasB]

Hi Josh

sorry that was not a very good link I gave you this is a better one:

http://www.mrexcel.com/forum/showthread.php?t=55704&highlight=looping+workbooks+collection

Rgs

Thomas

 

[Butters]

The 2nd link is broken.

 

[ThomasB]

Strange works for me when I click on it ,here it is again

http://www.mrexcel.com/forum/showthread.php?t=55704&highlight=looping+workbooks+collection

if it does not work then click on "search" at the top of the page
type "looping through workbooks collection" press enter and is the 3rd link from the bottom

Rgs

Thomas

 

[ThomasB]

Hi Josh

This code should do the trick

assumptions the data to be extracted is in a worksheet called "A" and cell A1, is being extracted to a workbook and worksheet called "Results".

you will have to set the file paths of the folder the workbooks are in and the Results workbook.

CODE:


Public Sub GetInfo()

Dim target As Workbook
Dim sCurFile As String
Dim sPath As String
Dim i As Integer
i = 1

Workbooks.Open ("C:\test\Results.xls")
sPath = "C:\Desktop" & "\"
sCurFile = Dir(sPath & "*.xls", vbNormal)
Do While Len(sCurFile) <> 0
i = i + 1
Workbooks.Open sPath & sCurFile, , True
Workbooks("Results").Worksheets("Results").Cells(i, 1).Value = Workbooks _ (sCurFile).Worksheets("A").Cells(1, 1).Value
sCurFile = Dir
DoEvents
Loop

End Sub