organizing scattered data in a spreadsheet with no column headings/ Need to organize/transfer it into one table?

Debug.Print File.Path
'Open File.Path workbook here and call your code on it

'Requires reference to Microsoft Scripting Runtime by doing this:
'1) Open the visual basic editor
'2) Select menu option Tools > References
'3) Find 'Microsoft Scripting Runtime' and tick it

Option Explicit

Public Sub Process_Files()

Application.ScreenUpdating = False

Dim Fso As Scripting.FileSystemObject

Set Fso = New Scripting.FileSystemObject
'Set the directory you wish to search below:
Process_XLS_Files Fso, "C:\Documents and Settings\Andrew\My Documents"

Set Fso = Nothing

Application.ScreenUpdating = True

MsgBox "Finished Processing Files", vbInformation, "Done!"

End Sub


Private Sub Process_XLS_Files(Fso As Scripting.FileSystemObject, folderPath As String)
   
Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
Dim wb As Workbook, OutputRow As Long
       
Set Folder = Fso.GetFolder(folderPath)
'Set the output row to the first row that you want this macro to write the results to
OutputRow = 2

For Each File In Folder.Files
    If InStr(File.Name, ".xls") And ThisWorkbook.Name <> File.Name Then
        Set wb = Workbooks.Open(File.Path)
        'Record which file the data came from in column A:
        ThisWorkbook.Sheets("Sheet1").Range("A" & OutputRow).Value = File.Path
        'Example of recording the value from cell A1 into column B
        ThisWorkbook.Sheets("Sheet1").Range("B" & OutputRow).Value = _
            wb.Sheets(1).Range("A1").Value
        'NB : note it records the data onto 'Sheet1' of the workbook that contains the macro
        'NB : it also assumes it is only looking at the first sheet of target spreadsheet
        '
        'Add additional lines for other cells you want to copy....
        '
        '
        wb.Close False
        OutputRow = OutputRow + 1
    End If
Next File

'************************************************************************************
'Delete this section if you do not want to process the spreadsheets in any subfolders
For Each Subfolder In Folder.subfolders
    For Each File In Subfolder.Files
        If InStr(File.Name, ".xls") And ThisWorkbook.Name <> File.Name Then
            Set wb = Workbooks.Open(File.Path)
            '*****************************************************
            'Whatever you did to the code above, repeat it below:
            ThisWorkbook.Sheets("Sheet1").Range("A" & OutputRow).Value = File.Path
            ThisWorkbook.Sheets("Sheet1").Range("B" & OutputRow).Value = _
                wb.Sheets(1).Range("A1").Value
            '*****************************************************
            wb.Close False
            OutputRow = OutputRow + 1
        End If
        
    Next File
Next Subfolder
'************************************************************************************

End Sub