Macro to search for and assign values from other workbook

Hey everyone, hopefully someone out there can help me out. I've only started using VBA a week or to ago and I haven't done any programming since Java and C++ classes freshman year of college.

For arguments sake say there are 5 locations (location1, location2, location3, location4, location5). For each location, there exists excel sheets we will call Reports. The reports are assigned a report number from 1 to 4000. The naming convention for these reports is (location#)(report number).xlsm. All of these reports are saved into one folder, say My Documents.
Say the directory of all these files is C:\Documents and Settings\My Documents
I want to create a summary workbook that has a macro that will go out and “search” for these individual reports from each location and then assign cells in the summary workbook to the values found in each individual report. Creating something similar to what is below…

Location Report# Date
Location 1 0809 7-23-2012
Location 1 2017 7-26-2012
Location 3 0500 7-22-2012
Location 4 4403 7-18-2012
Location 5 0651 7-20-2012



The code I have so far is this:

Sub AutoUpdate()

Dim Location As String
Dim Work As Integer
Dim Row As Integer
Dim FPath As String
Dim i As Integer

FPath = "C:\Documents and Settings\My Documents\"
Row = 1

For i = 1 To 5
If i = 1 Then
Location = "Location1"
End If
If i = 2 Then
Location = "Location2"
End If
If i = 3 Then
Location = "Location3"
End If
If i = 4 Then
Location = "Location4"
End If
If i = 5 Then
Location = "Location5"
End If

For Work = 1 To 4000

Range("A & Row").Select

If Dir(FPath &"\" & Structure & Work & ".xlsm") <> "" Then
ActiveCell.FormulaR1C1 = "='[Structure & Work & ".xlsm"]'Sheet1!R2C1
Work = Work + 1
Row = Row + 1
Selection.Offset(1, 0).Select
Else
Work = Work + 1
End If

Next

Next i

End Sub

I'm not sure how to make it so once say the cells in row A are filled to go down to row B. Also, am I retrieving the values from the other workbooks correctly?

Thanks