Automatically name active worksheet, and name active cell based on active worksheet

Question

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 "."

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

For example, when I merge pets to my current excel worksheet, the new sheet will be automatically named pets based on the `worksheet name`. -- Is it typo? workbook or worksheet name? — taller_ExcelHome, Aug 07 '23 at 19:11

taller_ExcelHome · Answer 1 · 2023-08-09T15:40:00.660

0

Sheets copied from other workbooks are set as ActiveSheet. Update the name and add refers name accordingly.

            Dim  sRef as String
            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)
                    fBaseName = Left(fnameCurFile, VBA.InStrRev(fnameCurFile, ".") - 1)
                    ' Update sheet name with file name (w/o ext)
                    ActiveSheet.Name = fBaseName
                    ' Add refers name 
                    sRef = "=" & fBaseName & "!$N$2:$N$15"
                    ' Name scope: Activesheet
                    ActiveSheet.Names.Add Name:=fBaseName, RefersTo:=sRef
                    ' Name scope: Workbook
                    ' wbkCurBook.Names.Add Name:=fBaseName, RefersTo:=sRef
                Next
                wbkSrcBook.Close SaveChanges:=False
            Next

edited Aug 09 '23 at 15:40

answered Aug 07 '23 at 21:21

taller_ExcelHome

2,232
1
2
12

Hey, I just tried it. Getting the user-defined type not defined and Dim fBaseName As Sting is the highlighted error. – alxn Aug 07 '23 at 21:36
Hey, I tried this one as well getting a "Syntax error" at ActiveSheet.Names.Add Name:=fBaseName RefersTo:=sRef. Tried adding this to the regular code and then by itself but still had the same error – alxn Aug 09 '23 at 13:31
@alxn Pls try the updated code. – taller_ExcelHome Aug 09 '23 at 15:01
Just tried it, now getting this error Run-time error '13' Type mismatch with "For Each fnameCurFile In fnameList" being the highlighted portion in the debugger – alxn Aug 09 '23 at 15:20
Remove `fBaseName As String ,` as you have declared it at the beginning – taller_ExcelHome Aug 09 '23 at 15:40
I'm still getting the same error :/ – alxn Aug 09 '23 at 15:50
Please carefully compare the code with OP. I haven't touch any code before `For Each wksCurSheet In wbkSrcBook.Sheets`. They should be same as before. – taller_ExcelHome Aug 09 '23 at 15:58
I managed to fix it but now I am getting another error: "Run-time error 1004 You typed an invalid name for a sheet or chart" on the line "ActiveSheet.Name = fBaseName" – alxn Aug 09 '23 at 16:17
If a sheet has same same (fBaseName) in your workbook, you will get 1004 with the code. – taller_ExcelHome Aug 09 '23 at 17:01
It's a bit weird, I get the error pop-up, but the sheet still gets added to the workbook with the appropriate name. Even if the name is different I get the pop-up but the sheet gets added with the correct name, it's a bit funny. – alxn Aug 11 '23 at 14:54

Automatically name active worksheet, and name active cell based on active worksheet

1 Answers1