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,577
  • 16
  • 23
grace
  • 1
  • 1
  • 1
  • 2
  • 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 07 '13 at 05:12

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
  • 26,823
  • 17
  • 108
  • 156
  • Won't this keep doing it every time the file's opened, even when it's no longer needed? – martineau Oct 05 '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 05 '13 at 16:13
  • Yeah, I suppose it would need to stay in place in case the filename gets changed later. +1 – martineau Oct 05 '13 at 16:55
  • I would test if `Count > 0`. If not, then use `ThisWorkbook.Name`. – Peon Oct 05 '13 at 17:19
  • @peon In case the filename is renamed via [`cmd`](http://superuser.com/a/406758/50173)? :) – nixda Oct 05 '13 at 18:11
  • @nixda because you can create in memory or open a valid Excel workbook even if it does not have the appropriate extension. – Peon Oct 06 '13 at 02:57
  • @Peon Ouh, you're right. Done – nixda Oct 06 '13 at 10:30
  • thanks for your help nixda.. 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 07 '13 at 05:21
  • @grace I've added your second request – nixda Oct 07 '13 at 06:53