Hi Need Help Fast

I advise not to use FileSearch

1: have bugs
2: not working in Excel 2007

See this page for other solutions
http://www.rondebruin.nl/copy3.htm

But if it is working for your problem that it is OK


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Need Help Fast!" wrote in message
...
Thanks Joel! It worked

"Joel" wrote:

This should help. You need to fix source and destinattion files, but it
works really good

Sub GetData()

Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "c:\Temp"
.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("Sheet1").Range(" B22:D24").Copy _

Destination:=Workbooks(ThisWorkbook.Name).Workshee ts("Sheet1"). _
Range("A10").Offset(rowOffset:=0, columnOffset:=0)

Workbooks(MyfileName).Close SaveChanges:=False

Next i


End Sub


"Need Help Fast!" wrote:

Hi,

I want to know how I can take data from multiple excel workbooks and have it
be put into one giant list in another excel workbook. The header is the same
for all of the data. It is different data for each state, so I have over 50.
Instead of copying and pasting each one into another workbook, I'd like to
automate it. Is there a way to write a macro that would take all of the excel
files in a folder and take from the exact same sheet in each excel file the
data and paste it onto one worksheet in another excel workbook? Thanks

-Need Help Fast!