Rename a worksheet based on a Cell?????

mbotts2332

New Member
Joined
Sep 9, 2002
Messages
2
Is it possible to rename a worksheet, but have it renamed automatically, and have it be renamed to what is entered into a cell in another worksheet automatically?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mbotts2332

New Member
Joined
Sep 9, 2002
Messages
2
Yes Macro's are possible, and that was what I was figuring that needed to be used. I will try creating a macro, any tips would be helpful.
Thanks!
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Not to step on Juan, but VBA would be the only way to do this. If you mean that, for instance, you want the name of the sheet tab for Sheet2 to change when a cell, say A1 in Sheet1 is changed, then enter this code in the module of Sheet1. Note that we need to use the VBA sheet object code in referencing the sheet being changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Sheet2.Name = [A1].Value
End Sub
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Ok then. Here are my assumptions, you have two sheets, one, where you will change the cells, in order to change the sheet names, named "Master", and the child sheets.

In this example I'll link the cell Master!A1 with the sheet 1 on the workbook.

Right click on the Master's tab, and select "View code". In there, paste this code:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets(1).Name = Target.Value
End Sub</pre>The 1 in Sheets means the first sheet, according to its position.
 

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
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
 

Forum statistics

Threads
1,143,915
Messages
5,721,519
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top