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:

Please Login or Register  to view this content.

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