ExcelBanter - collating/consolidating large amount of data in excel

here is a simple macro that will work. Modify the code below as follows

1) Put all the xls files in 1 folder (directory). and change MyPath line
below to match directory. the program will open every xls file in the
directory.
2) Change sheetnames to mattch the sheet names in your worksheets

I put the F column data into column A
I put the J column data in column E
I put the M column data into column C

Sub GetData()

MyPath = "c:\temp\test_xls"
SourceSheetname = "Sheet1"
DestinationSheetname = "Sheet1"

JOffset = 0
FMOffset = 0

DestinationName = ThisWorkbook.Name
Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = MyPath
.FileType = msoFileTypeExcelWorkbooks
.Execute
End With

For i = 1 To fs.FoundFiles.Count

MyfileName = fs.FoundFiles(i)
Workbooks.Open Filename:=MyfileName, ReadOnly:=True

' Removed pathname from file name so it can be referenced in this
program.
'Basic doesn't like the full pathname???? stupid microsoft
Do While (1)
CharPosition = InStr(MyfileName, "\")
If CharPosition 0 Then
MyfileName = Mid(MyfileName, CharPosition + 1)
Else
Exit Do
End If
Loop

Workbooks(MyfileName).Worksheets(SourceSheetname). Range("F15:F31").Copy _

Destination:=Workbooks(DestinationName).Worksheets (DestinationSheetname). _
Range("A1").Offset(rowOffset:=FMOffset,
columnOffset:=0)

Workbooks(MyfileName).Worksheets(SourceSheetname). Range("M15:M31").Copy _

Destination:=Workbooks(DestinationName).Worksheets (DestinationSheetname). _
Range("C1").Offset(rowOffset:=FMOffset,
columnOffset:=0)

Workbooks(MyfileName).Worksheets(SourceSheetname). Range("J40:J42").Copy _

Destination:=Workbooks(DestinationName).Worksheets (DestinationSheetname). _
Range("E1").Offset(rowOffset:=JOffset,
columnOffset:=0)

FMOffset = FMOffset + 17
JOffset = JOffset + 3

Workbooks(MyfileName).Close SaveChanges:=False

Next i

End Sub

" wrote:

Hi,
I have just conducted a survey and have recieved about 70 excel
workbooks containing the data i reqested in the survey. The data is
all contained in the same cells in each workbook. There are 3 sets of
data i need to collate from each of these workbooks. one is in the
cells F15:F31, the other is in M15:M31 and the last is in J40:J42. I
would like to collect all the data in the M cells from one workbook
and put them directly under the ones from another workbook and so on.
Is there an easy way of doing this? please bear in mind that i have no
clue how to use macros talkless of even run one. Would be grateful if
someone could help.

Thanks,
Joe.

ps; u guys are great for taking the time to help people like this.
Would like to help anyone if I could.