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

[alxn]

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

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

 

[Joe4]

Here is a block of code that shows how you can take the name of the activeworkbook, drop the extension, and rename the activesheet to that name:

    Dim shtName As String
    shtName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
    ActiveSheet.Name = shtName

Since we are finding the last period from the end of the string (going in reverse), it should not matter if the file name has a date that has periods in it.

 

[alxn]

Here is a block of code that shows how you can take the name of the activeworkbook, drop the extension, and rename the activesheet to that name:

    Dim shtName As String
    shtName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
    ActiveSheet.Name = shtName

Since we are finding the last period from the end of the string (going in reverse), it should not matter if the file name has a date that has periods in it.

Hey, I'm getting these errors when using this code.

 

[Joe4]

Temporarily comment out the save line, and add a MsgBox instead and tell me what it returns:

    Dim shtName As String
    shtName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
    MsgBox shtName
    'ActiveSheet.Name = shtName

 

[alxn]

Temporarily comment out the save line, and add a MsgBox instead and tell me what it returns:

    Dim shtName As String
    shtName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
    MsgBox shtName
    'ActiveSheet.Name = shtName

I did a workaround and just added a command that renamed "TRUE" to "TRUE TRUE". The space between both of them was able to trigger the checkbox.