Collating data from varying number of sheets

Hi munkee
From what you've described, I believe this code can be modified to create the merged file for your further analysis

Option Explicit
Dim sFileName As String

'********************************************************
'Reference to Microsoft Scripting Runtime must be set
'Tools->References-> select Microsoft Scripting Runtime
'********************************************************

Public Sub ImportData()
    Dim wbThis As Workbook              'the target workbook
    Dim wswbThis As Worksheet           'the target workbook sheets
    Dim wb2 As Workbook                 'the source workbooks
    Dim wsWb2 As Worksheet              'the source workbook sheets
    Dim fso As New FileSystemObject     'create file sys object
    Dim myFolder As Folder              'name of file sys object
    Dim myFile As File                  'files being imported
    Dim myPath As String                'path of the target workbook

    MsgBox "Select The File You Wish To Import To" & vbCrLf & _
    "Target & Source Files Must Be In Same Folder"
    
    Call RetrieveFileName
    If sFileName = "False" Then Exit Sub

    myPath = ActiveWorkbook.Path
    Set myFolder = fso.GetFolder(myPath)
    Set wbThis = ActiveWorkbook

    Application.ScreenUpdating = False
    For Each myFile In myFolder.Files    'loop through the files in the folder
        If myFile.Name <> "MacroBook.xls" And myFile.Name <> wbThis.Name _
           And myFile.Type Like "*" & "Excel" & "*" Then  'include only excel files
            Workbooks.Open myFile, UpdateLinks:=False

            Set wb2 = ActiveWorkbook    'make found file active
            For Each wswbThis In wbThis.Sheets
                For Each wsWb2 In wb2.Sheets    'loop thru it's sheets
                    If wsWb2.Name = wswbThis.Name Then    'find a sheetname match
                        ' insert your code here
                        ' insert your code here
                        ' insert your code here
                        ' insert your code here
                    Else
                    End If
                Next
            Next
            Workbooks(myFile.Name).Close False    'saves source files without change
        End If
    Next myFile
    Application.ScreenUpdating = True
End Sub

Sub RetrieveFileName()
    sFileName = Application.GetOpenFilename
    If sFileName = "False" Then Exit Sub
    Workbooks.Open sFileName
End Sub

If you need assistance in modifying the code, attach a couple of sample source files and an example of what the merged data is to look like. I'll be glad to look at it with you.
John