Pull data from multiple spreadsheets

[Emoncada]

I nee some help and ideas on how to accomplish my goal. I daily have over 40 new spreadsheets with data, all have one worksheet and all saved with different names. All these spreadsheet are saved into a date folder (example Folder Name = Dec 18).
I need to pull data from all these files on a daily basis. So on Dec 19th I need the data from the Dec 18th Folder. ALL data falls in same cells.
So in all files I would need cells (b8-b10,d7-d10,e7,a242-d260, & a244.)
I need some ideas on how I can pull all the data from those spreadsheets into one without changing the files names and not manually having to go into each one grabbing the data. Any Ideas?

[Bob Phillips]

[vba]

Public Sub Test()
Dim mpFile As String
Dim mpFolder As String
Dim mpThisWB As Workbook
Dim mpNewWB As Workbook
Dim mpNextLine As Long
Dim mpNextCol As Long
Dim mpCell As Range
Dim i As Long

Set mpThisWB = ActiveWorkbook
mpFolder = "C:\test\" & Format(Date - 1, "mmm dd")
mpFile = Dir(mpFolder & "\*.xls")
mpNextLine = 1
Do While mpFile <> ""

Set mpNewWB = Workbooks.Open(mpFolder & "\" & mpFile)
mpNextCol = 1
With mpThisWB.Worksheets(1)

For Each mpCell In mpNewWB.Worksheets(1).Range("B8:B10, D710, E7, A242260, A244")

.Cells(mpNextLine, mpNextCol).Value = mpCell.Value
mpNextCol = mpNextCol + 1

Next mpCell
End With
mpNewWB.Close

mpNextLine = mpNextLine + 1

mpFile = Dir
Loop

Set mpThisWB = Nothing
Set mpNewWB = Nothing

End Sub
[/vba]

[Emoncada]

wow that was pretty nice. But is there a way that I can have it place things a certain way? Basically after any new row after A242 to fall right under A242.
So example Row A242 falls under columns (in spreadsheet with macro)I:L . If there is a A243 row then can I have it fall under columns I:L ? Then
A244 fall under columns I:L.

[Bob Phillips]

Do you mean to start after the last occupied row?

[Emoncada]

i mean like making Column L the last possible Column for data. So when pulling data from the spreadsheets. It would pull row A242 : D242 which D242 would fall under col L. Then if there is data in A243 : D243 that would fall under the data from A242 : D242.