Worksheet Renaming

[JSS]

Please suggest functions to automatically rename a worksheet to the
workbook name.

 

[Nick Hodge]

JSS

This could be done with code either by button, or an automated event, but I
am confused as to how this will work. Will it just name the active sheet or
do all your workbooks open with a single sheet? Bearing in mind a sheet
name must be unique

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[JSS]

Nick

Thanks for the reply. I just want automatic renaming of a worksheet
with the workbook name (or file name) which is active.

eg. if file name is "abc.xls" then I want name of the worksheet name to
be "abc".

Thanks and regards

JSS

 

[Nick Hodge]

JSS

I gathered that from the first post, but how/when do you want this to
happen? and which worksheet?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Dave Peterson]

You could try to rename the first worksheet in the workbook using code like:

Option Explicit
Sub auto_open()

Dim myNewName As String
myNewName = ThisWorkbook.Name
If LCase(Right(myNewName, 4)) = ".xls" Then
myNewName = Left(myNewName, Len(myNewName) - 4)
End If
On Error Resume Next
Worksheets(1).Name = myNewName
If Err.Number <> 0 Then
MsgBox "Worksheet was not renamed!"
Err.Clear
End If
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

Ps. Since this procedure is named Auto_Open(), it will run each time the
workbook is opened--not when the workbook is saved as a new name.

 

[JSS]

Dave

Thanks a lot for the suggestion.

JSS