Merge and Copy VBA

[aliaslamy2k]

I am creating an Assessment result database for candidates. When I download the report from the system, the reports are downloaded based on
designations. Therefore, there will be multiple reports I need to download.

I have a VBA that will merge all the files from the folder. I would like to modify the below VBA so it will merge all excel
files with their own file name as "Sheet Name" when it's merged.

After its merged, i want to copy data from all the sheets merged in one excel such as "First Name", "Last Name", "Email", "Position 'As Excel sheet name", "Invitation Date", "Completion Date", "Status", "Stage"

Please help me to write this code.

DOWNLOADED REPORT FORMAT

First name	Last name	Email	Source	Invitation date	Completion date	Status	Stage	Scoring benchmark	Average score	Qualifying questions	CDP - Skills Test	Communication	English (elementary/A2)	Filled out only once from IP address?	Webcam enabled?	Full-screen mode always active?	Mouse always in assessment window?	Your rating	Notes about the candidate
Pradip	Biswas	abcd@gmail.com	Invitation by email	2022/06/27	2022/06/29	Completed	Rejected	All candidates	21		40	3	20	Yes	Yes	No	No	1	Not all tests completed
MD	Anis	abcd@gmail.com	Invitation by email	2022/06/27		Invited	Not yet evaluated	All candidates						Yes	No	Yes	Yes
Matiur	Rahman	abcd@gmail.com	Invitation by email	2022/06/27		Invited	Not yet evaluated	All candidates						Yes	No	Yes	Yes
Anirban	Das	abcd@gmail.com	Invitation by email	2022/06/27		Invited	Not yet evaluated	All candidates						Yes	No	Yes	Yes
Atul	Gupta	abcd@gmail.com	Invitation by email	2022/06/27		Addressed	Not yet evaluated	All candidates						Yes	No	Yes	Yes
Sourav	Ghosh	abcd@gmail.com	Invitation by email	2022/06/27		Invited	Not yet evaluated	All candidates						Yes	No	Yes	Yes
Shahruk	Khan	abcd@gmail.com	Invitation by email	2022/06/27		Invited	Not yet evaluated	All candidates						Yes	No	Yes	Yes

DESIRED RESULT IN MASTER SHEET

First Name	Last Name	Email	Position	Invitation date	Completion Date	Status	Stage

CURRENT VBA CODE

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