populate summary separated range for each name across files for same sheet name

[Aussiebear]

This will get you started, note however that it doesn't include the Summary data that you want as you will need to define the Summary data better.

Sub SummarizeExtractionData()
    Dim fso As Object 
    ' FileSystemObject
    Dim folderPath As String
    Dim objFolder As Object 
    ' Folder object
    Dim objFile As Object 
    ' File object
        Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim todaysDate As String
    Dim summaryFileName As String
    Dim filePath As String
    Dim fileExists As Boolean
    ' Set the folder path
    folderPath = "C:\Users\MMGG\Desktop\Summary days\"
    ' Get today's date in YYYYMMDD format for the filename
    todaysDate = Format(Date, "YYYYMMDD")
    summaryFileName = "Summary_" & todaysDate & ".xlsx"
    ' Construct the full path for the summary file
    filePath = Environ("USERPROFILE") & "\Desktop\" & summaryFileName 
    ' Assuming you want to save on the Desktop
    ' Check if the summary file already exists
    fileExists = Dir(filePath) <> ""
    ' Create or open the target workbook
    If fileExists Then
        Set targetWorkbook = Workbooks.Open(filePath)
        ' Check if the "Summary" sheet exists, if not add it
        On Error Resume Next
        Set targetSheet = targetWorkbook.Sheets("Summary")
        On Error GoTo 0
        If targetSheet Is Nothing Then
            Set targetSheet = targetWorkbook.Sheets.Add(After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count))
            targetSheet.Name = "Summary"
        End If
    Else
        Set targetWorkbook = Workbooks.Add
        Set targetSheet = targetWorkbook.Sheets.Add        
        targetSheet.Name = "Summary"
    End If
    ' Create a FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Get the folder object
    Set objFolder = fso.GetFolder(folderPath)
    ' Loop through each file in the folder
    For Each objFile In objFolder.Files
        ' Check if the file is an Excel file (you might want to refine this check)
        If InStr(1, objFile.Name, ".xls", vbTextCompare) > 0 Then
            ' Open the source workbook (without updating links or read-only prompt)
            Set sourceWorkbook = Workbooks.Open(objFile.Path, UpdateLinks:=False, ReadOnly:=True)
            On Error Resume Next 
            ' Handle the case where the "Extraction" sheet doesn't exist
            Set sourceSheet = sourceWorkbook.Sheets("Extraction")
            On Error GoTo 0
            ' Check if the "Extraction" sheet was found
            If Not sourceSheet Is Nothing Then
                ' Find the last used row in the target sheet
                lastRow = targetSheet.Cells(Rows.Count, 1).End(xlUp).Row
                ' If it's the first time adding data, start from row 1, otherwise go to the next empty row
                If lastRow > 1 Or (lastRow = 1 And IsEmpty(targetSheet.Cells(1, 1))) Then
                    lastRow = lastRow + 1
                Else
                    lastRow = 1
                End If
                ' Get the last used row in the source sheet
                Dim sourceLastRow As Long
                sourceLastRow = sourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
                ' Copy all data from the "Extraction" sheet to the target sheet
                sourceSheet.UsedRange.Copy targetSheet.Cells(lastRow, 1)
            Else
                MsgBox "Sheet 'Extraction' not found in file: " & objFile.Name, vbExclamation
            End If
            ' Close the source workbook without saving
            sourceWorkbook.Close 
            SaveChanges:=False
            Set sourceWorkbook = Nothing
            Set sourceSheet = Nothing
        End If
    Next objFile
    ' Save the target workbook    
    targetWorkbook.SaveAs filePath
    ' Release object variables
    Set fso = Nothing
    Set objFolder = Nothing
    Set objFile = Nothing
    Set targetSheet = Nothing
    Set targetWorkbook = Nothing
    MsgBox "Data from 'Extraction' sheets in '" & folderPath & "' has been summarized in '" & filePath & "'", vbInformation
End Sub

As I indicated earlier, if you were to perhaps name the Summary data range as a table then we could look at extracting the table and then look at the layout of the data on the destination sheet.