Because I was bored I wrote a macro that

A) assumes the file names goes from 1.xlsx, 2.xlsx, ............. 200.xlsx

B) there is only one sheet in all these 200 files

You need to specify the folder where the files are found i.e. you must change the sPath "=C:\Temp\" to this folder, don't forget the last "\" if missing from sPath macro will not run.

Result will be written to firs empty column in target file (the file that contains this macro) In the first row you will get the file name i.e. "1.xlsx" in cell A1 and all the data from this file in A2 and downwards. "2.xlsx" in B1 and so fort. If a file is missing i.e. "125.xlsx" but not "126.xlsx" macro will ignore missing file and move on to next file.

At the moment loop is set for 200 files. If there are other files in this folder these will be ignored only files with names from "1.xlsx" to "200.xlsx" will be processed.

Option Explicit

Sub ProcessFiles()

    Dim sPath As String
    Dim Wb As Workbook
    Dim sFile As String
    Dim i As Integer
    Dim k As Integer
    
    
    sPath = "C:\Temp\"
  
    Application.ScreenUpdating = False

    Do While i <= 200
    
    i = 1 + i
    
    sFile = Dir(sPath & i & ".xlsx")
    
    If sFile = "" Then GoTo skipper
    
        Set Wb = Workbooks.Open(sPath & sFile)
         ActiveSheet.UsedRange.Copy
        Wb.Close SaveChanges:=False
     
    If Range("A1") = "" Then
      Range("A1").Value = sFile
      Range("A2").PasteSpecial xlPasteAll
   Else
      k = Cells(1, Columns.Count).End(xlToLeft).Column + 1
      Cells(1, k).Value = sFile
      Cells(2, k).PasteSpecial xlPasteAll
   End If
   
    sFile = Dir
    
skipper:
        
    Loop

Application.CutCopyMode = False

   Application.ScreenUpdating = True
    
End Sub
Alf