Merge multi Excel files into one worksheet with VBA

I tried to merge multiple Excel files into one single worksheet with VBA but it only put all sheets into one workbook. I also want to add one first column with the name of individual Excel files.

How can I resolve this?

Sub MergeExcelFiles()
  Dim fnameList, fnameCurFile As Variant
  Dim countFiles, countSheets As Integer
  Dim wksCurSheet As Worksheet
  Dim wbkCurBook, wbkSrcBook As Workbook

  fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

  If (vbBoolean <> VarType(fnameList)) Then

    If (UBound(fnameList) > 0) Then
      countFiles = 0
      countSheets = 0

      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual

      Set wbkCurBook = ActiveWorkbook

      For Each fnameCurFile In fnameList
          countFiles = countFiles + 1

          Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

          For Each wksCurSheet In wbkSrcBook.Sheets
              countSheets = countSheets + 1
              wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
          Next

          wbkSrcBook.Close savechanges:=False

      Next

      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic

      MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
    End If

  Else
      MsgBox "No files selected", Title:="Merge Excel files"
  End If
End Sub

This mod. will copy the used range of all sheets in one sheet (newly added to the active workbook, and then insert the sheet name in the first column.

Sub MergeExcelFiles()
  Dim fnameList, fnameCurFile As Variant
  Dim countFiles, countSheets As Integer
  Dim wksCurSheet As Worksheet
  Dim wbkCurBook, wbkSrcBook As Workbook
  Dim targetsheet As Worksheet, CopyToCell As Range, NewRowsCount As Long  'added
  fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

  If (vbBoolean <> VarType(fnameList)) Then

    If (UBound(fnameList) > 0) Then
      countFiles = 0
      countSheets = 0

      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual

      Set wbkCurBook = ActiveWorkbook
      Set targetsheet = wbkCurBook.Worksheets.Add(, wbkCurBook.Worksheets(wbkCurBook.Worksheets.Count))   'added
      Set CopyToCell = targetsheet.Range("A1")      'added

      For Each fnameCurFile In fnameList
          countFiles = countFiles + 1

          Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

          For Each wksCurSheet In wbkSrcBook.Sheets
              countSheets = countSheets + 1

              'Changes from here
              'wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)  'Removed
              NewRowsCount = wksCurSheet.UsedRange.Rows.Count     'added
              wksCurSheet.UsedRange.Copy CopyToCell               'added
              Set CopyToCell = targetsheet.Cells(targetsheet.UsedRange.Rows.Count + 1, 1)
              targetsheet.Range("A" & targetsheet.UsedRange.Rows.Count - NewRowsCount + 1 & ":A" & targetsheet.UsedRange.Rows.Count).Insert xlToRight
              targetsheet.Range("A" & targetsheet.UsedRange.Rows.Count - NewRowsCount + 1 & ":A" & targetsheet.UsedRange.Rows.Count) = wksCurSheet.Name
              'Changes end

          Next

          wbkSrcBook.Close savechanges:=False

      Next

      'add column header
      targetsheet.Rows(1).Insert xlDown
      targetsheet.Range("A1") = "Document name"
      'column header added

      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic

      MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
    End If

  Else
      MsgBox "No files selected", Title:="Merge Excel files"
  End If
End Sub

If you want the filename and the sheetname together, you can concatenate them (without adding a new column).
For this replace wksCurSheet.Name to
wbkSrcBook.Name & "\" & wksCurSheet.Name or
wbkSrcBook.FullName & "\" & wksCurSheet.Name with the full path info.