Here's another example, which will save the old sheet name in the windows registry so it will still work when you come back to it after closing/reopening Excel. Note that you have to run the first part setting the registry variable and initial sheet name only once, then must disable that part from then on. This macro could then be called as a result of opening a workbook or some other event. Hope this helps... Cheers,
--Ray
Sub namesheettest()
'THIS RANAMES A WORKSHEET TO THE VALUE STORED IN A CELL. IT KEEPS TRACK
' OF THE SHEET NAME PERMANENTLY AND RESETS IT EVERY TIME IT IS RENAMED. IT
'ASSUMES YOU ALREADY HAVE A SHEET NAMED "MYSHEETNAME", AND THE FIRST SECTION
'THAT CAPTURES THE SHEET NAME TO THE REGISTRY NEED ONLY BE DONE ONCE AND NEEDS TO
'BE DISABLED OR REMOVED AFTER THE FIRST TIME IT IS RUN.
'******THIS PART NEEDS TO BE DONE ONLY ONCE TO SET INITIAL VALUES*****
OldName = "MYSHEETNAME"
'SETS THE INITIAL VALUE OF THE OLD SHEET NAME
SaveSetting "MyName", "MyProject", "OldSheetName", OldName
'Sets the Windows registry to create the appname (MyName), section
' (MyProject), key (OldSheetName) and setting (OldName). It saves the value of OldName
'in the registry variable "OldSheetName" until it is reset via the same command.
'AFTER THIS PART IS DONE ONCE, THEN DISABLE OR REMOVE IT
'*********************************************************
OldName = GetSetting("Myname", "MyProject", "OldSheetName")
'THIS GETS THE OldSheetName VALUE THAT HAS BEEN STORED IN THE WINDOWS REGISTRY
'and sets the OldName variable to that value
NewName = Range("$b$3")
'ASSIGNES SHEETNAME VARIABLE TO CONTENTS OF B3. YOU COULD USE ANY CELL REFERENCE
'OR RANGE NAME HERE YOU WANTED
Worksheets(OldName).Name = NewName
'RENAMES THE PREVIOUS WORKSHEET NAME TO THE NEW NAME
OldName = NewName
'RESETS THE VALUE OF THE OLDNAME VARIABLE TO THE NEW NAME
SaveSetting "MyName", "MyProject", "OldSheetName", OldName
'RESETS THE VALUE OF OLDSHEETNAME TO THE CURRENT SHEET NAME TO STORE IT
'PERMANENTLY IN THE WINDOWS REGISTRY
End Sub