Extract Data From Multiple Excel Files -> One File

[Michael via OfficeKB.com]

I need a way to extract data from multiple excel files (31) and put the data
into one sheet. All the data I need is in the same spots (B6:F29). I am
familiar with macros but not programming. Is there a simple way to do this?

Thank You

 

[Ron de Bruin]

Look here Michael
http://www.rondebruin.nl/copy3.htm

 

[bhofsetz]

Where are the source files located? If they are all in the same
directory then you can loop through all the workbooks in that directory
and copy from the desired sheets the range specified and paste into the
new workbook.


Code:
--------------------
Sub ConsolidateData()
Dim FileSystem As Object, myFolder As Object
Dim myFiles As Object, myFile As Object
Dim wkb, Col As Integer, result
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set myFolder = FileSystem.getfolder(CurDir)
Set myFiles = myFolder.Files
For Each myFile In myFiles
Col = ThisWorkbook.Sheets(1).UsedRange.Columns.Count
Workbooks.Open (myFile)
ActiveWorkbook.Sheets(1).Range("B6:F29").Copy
ActiveSheet.Paste Destination:=ThisWorkbook.Sheets(1).Cells(1, Col) '= ActiveWorkbook.Sheets(1).Range("B6:F29")
Workbooks.Open(myFile).Close False
Next myFile

End Sub
--------------------


Give this a try and see if it gives you the results you are after.
This will use the Current Directory as the source for the files to pull
data from, and pulls data from the first sheet of each file found in the
current directory and pastes it in the next avaliable colulmn in the
workbook from which you are running the code.

If you need help with further modifications post back.

 

[Michael via OfficeKB.com]

Thank you, I will give that a try!