Merging multiple excel files into 1, almost there just need some help
 

Thanks in advance for any help you can offer me, it is greatly
appreciated.

I took some sample code from here an earlier discussion he
http://groups.google.com/group/micro...72d199e2601a33

However I want to import from an excel file that looks like:
A1 A2
company Company inc
address 12312 grey blvd

And change it into:
A1 A2
Company inc 12312 grey blvd

Basically get all of the company information from various excel files
and then insert them as one row each into a master file that holds
all of the companies. I know some VBA but I am not that great at it.
Please tell me where I need to change in my code. I believe it is
this line:

Set destrange = basebook.Worksheets(2).Cells(1, rnum). _
Resize(.Rows.Count, .Columns.Count)


Sub File_crawl_consolodate_values()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim j As Long
Dim a As Long
Application.ScreenUpdating = False
With Application.FileSearch
..NewSearch
..LookIn = "C:\Data\excels"
..SearchSubFolders = True
..FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To 2 '.FoundFiles.Count
For j = 1 To 1
Select Case j

Case 1
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(2).Range("B1:B7")
a = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(2).Cells(1, rnum). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close
rnum = rnum + a
End Select
Next j
Next i
End If
End With
End Sub