0
I'm trying to run a code that merges Excel files and turns them into sheets, so far that step works perfectly. But, I would like for the sheets to be called the actual document name when they are merged into the active document, for example, once I merge a document the sheet that the document becomes is named the same as the document automatically. Additionally, I would love for N2:N15 to be named the sheet name or in this case the active sheet.


For example, when I merge pets to my current Excel worksheet, the new sheet will be automatically named pets based on the workbook name. There are like 50 total groups so that's why I would like it to be automatic, some of the files have dates, which may also be the reason why I can't get it to work because of the "."
I'm trying to run a code that merges Excel files and turns them into sheets, so far that step works perfectly. But, I would like for the sheets to be called the actual document name when they are merged into the active document, for example, once I merge a document the sheet that the document becomes is named the same as the document automatically. Additionally, I would love for N2:N15 to be named the sheet name or in this case the active sheet.


For example, when I merge pets to my current Excel worksheet, the new sheet will be automatically named pets based on the workbook name. There are like 50 total groups so that's why I would like it to be automatic, some of the files have dates, which may also be the reason why I can't get it to work because of the "."
VBA Code:
Sub Merge_Excel_Files()
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;*.csv),*.xls;*.xlsx;*.xlsm;*.csv", 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