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.Name = myname
End Sub

but it's not automatically renamed.

  • thanks for your help.. i have another question. what formula can i put to my cell A2, so that my file name appear also in this cell automatically? – grace Oct 7 '13 at 5:12

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

  • Won't this keep doing it every time the file's opened, even when it's no longer needed? – martineau Oct 5 '13 at 16:10
  • @martineau As I understand the question this is exactly what he/she wants: Automatically rename [...] and but it's not automatically renamed. – nixda Oct 5 '13 at 16:13
  • Yeah, I suppose it would need to stay in place in case the filename gets changed later. +1 – martineau Oct 5 '13 at 16:55
  • I would test if Count > 0. If not, then use ThisWorkbook.Name. – Peon Oct 5 '13 at 17:19
  • @peon In case the filename is renamed via cmd? :) – nixda Oct 5 '13 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.