Extract data from Multiple excel files

I made a tiny change to your SHEET1 layout. This macro will collect the data as described from your defined folder path (fPATH). Be sure to edit the path in the macro.

Option Explicit

Sub CollectData()
Dim wbData As Workbook, ws2 As Worksheet, ws3 As Worksheet, wsDEST As Worksheet
Dim NR As Long, fPATH As String, fNAME As String

Set wsDEST = ThisWorkbook.Sheets("Sheet1")                  'sheet to build results
NR = wsDEST.Range("C" & Rows.Count).End(xlUp).Row + 1       'next empty row

fPATH = "C:\TEMP\XXvsYY\"       'path to data files, remember the final \ in this string
fNAME = Dir(fPATH & "*.xlsx")   'get first filename

Do While Len(fNAME) > 0
    Set wbData = Workbooks.Open(fPATH & fNAME)              'open the found file
    wsDEST.Range("A" & NR).Value = fNAME                    'filename in col A, timestamp in col B
    wsDEST.Range("B" & NR).Value = Format(FileDateTime(wbData.FullName), "m/d/yy h:mm am/pm")
    With wsDEST.Range("C" & NR).Resize(, 4)                 'collect info from sheet2
        .Formula = "=COUNTIF('[" & fNAME & "]" & wbData.Sheets(2).Name & "'!C6, R2C)"
        .Value = .Value
    End With
    With wsDEST.Range("G" & NR).Resize(, 4)                 'collect info from sheet3
        .Formula = "=COUNTIF('[" & fNAME & "]" & wbData.Sheets(3).Name & "'!C6, R2C)"
        .Value = .Value
    End With

    wbData.Close False          'close the data file
    NR = NR + 1                 'next empty row
    fNAME = Dir                 'get next filename
Loop

End Sub