0

I want to rename my first sheet or tab in excel based on the file name. If I change the file name, I want the first sheet to be automatically renamed. I don't want .xlsx to be included. I tried this macro:

Sub RenameSheet()
Dim myname
myname = Replace(ActiveWorkbook. Name, ".xls", "")
    ActiveSheet.Select
    ActiveSheet.Name = myname
    Range("A1").Select
End Sub

but it's not automatically renamed.

John Bensin
  • 1,645
grace
  • 1

1 Answers1

2

Insert this code under ThisWorkbook. It will rename your first sheet according to your current filename.

Remember that you can not change a filename while the file itself is opened in Excel. Due to this, you can utilize the Workbook_open() event which is triggered one time when the file is opening.

InStrRev searches from right to left the first occurens of a Point (.) to know where to cut off the extension with a Left function.

Private Sub Workbook_open()
    Count = InStrRev(ThisWorkbook.Name, ".")
    If Count > 0 Then
        Sheets(1).Name = Left(ThisWorkbook.Name, Count - 1)
        Sheets(1).Range("A2") = Left(ThisWorkbook.Name, Count - 1)
    End If
End Sub

enter image description here

nixda
  • 27,634