Rename Worksheet Tab as File Name Using a Macro

[Cue]

Hello,

I am trying to rename a worksheet tab using the name of the workbook. Each
code I've tried does not work. Does anybody have a simple macro to accomplish
this?

Thanks.

 

[Bob I]

Perhaps assign file name to variable, then assign variable to Tab.

 

[CLR]

Sub RenameSheet()
Dim myname
myname = ThisWorkbook.Name
ActiveSheet.Select
ActiveSheet.Name = myname
Range("A1").Select
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[FSt1]

hi
Sheets("sheet1").Name = ActiveWorkbook.Name

regards
FSt1

 

[Cue]

Great! Thanks to both of you for your assistance.

One thing i did not account for... Is there a way rename the tab with the
filename with the ".xls"?

 

[Cue]

Excuse me...
Is there a way rename the tab with the filename without the ".xls"?

 

[Cue]

I'll try that. Thanks.
--
Cue

Perhaps assign file name to variable, then assign variable to Tab.


.

 

[FSt1]

hi
now you will have to use variables.
Sub nametab()
Dim s As String
Dim ss As String
s = ActiveWorkbook.Name
ss = Left(s, Len(s) - 4)
Sheets("Sheet1").Name = ss
End Sub

regards
FSt1

 

[מיכ×ל (מיקי) ×בידן]

I would suggest to try the following code.
1) It will work, as expected, in "Excel 2007 and 2010"
2) It may be run from within a Module and it will rename the Active Sheet at
that moment.
There is alao another "workaround" by checking the Excels version and then
cut-off 4 or 5 characters from the end.

 

[Cue]

Thanks everyone! All your solutions worked!

I found this similar to CLR solution:

Sheets("Sheet2").Name = Replace(ActiveWorkbook. Name, ".xls", "")

Thanks again everyone for your help!

 

[Cue]

Thanks everyone. Of course, all of your solutions worked. Now I have to
decide which one to use.

Thanks again to all of you!