Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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