excel - make the content of a cell equal to worksheet name

A

arunjoshi

I have a total of 20 worksheets. The worksheet names are "q1", "q2"
"q3" ... , "q19, "q20" (all without the quotes). In each sheet, I wan
cell A1 to contain the name of the worksheet.

When I change the name of any worksheet, the content of cell A1 (in th
particular sheet) should also automatically change to the new name o
the worksheet
 
P

pauluk

If you place the following code into vba it should work. What it does i
after you have changed the sheet names goto tool and there will be
command there called re-name cell click on this this will then renam
the active sheet. you may have to change it into a loop to do othe
sheets but only the name sub.

Regards
Paul

Sub Auto_Open()

Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Dim CmdBarMenuItem As CommandBarControl
'
' Point to the Worksheet Menu Bar
'
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
'
' Point to the Tools menu on the menu bar
'
Set CmdBarMenu = CmdBar.Controls("Tools")
'
' Add a new menu item to the Tools menu
'
Set CmdBarMenuItem = CmdBarMenu.Controls.Add
'
' Set the properties for the new control
'
With CmdBarMenuItem
.Caption = "Re-Name Cell"
.OnAction = "'" & ThisWorkbook.Name & "'!Name"
.Tag = "Name"
End With
End Sub
Sub Auto_Close()
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl

Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("Tools")
CmdBarMenu.Controls("Re-Name Cell").Delete
End Sub
Sub Name()
ActiveSheet.Select
Range("A1").Value = ActiveSheet.Name
End Su
 
F

firefytr

hi arunjoshi,

you could also do this with a formula in A1. a couple of options here
let's look at the following:

with excel's native functions:

=MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,32)

with the Morefunc.xla addin from laurent longre:

=SHEETNAME()

in the first formula, the +1 is used to start at the first letter afte
the ] in the filename. the filename is between the [] sign's. the 3
is because a sheet name cannot be longer than that many characters.

i prefer the second formula - for obvious reasons. you can copy/past
this formula to all sheets, or even run it through a looping macro.

ht
 
D

Dave Peterson

I think you'll want to put a reference to a cell on each worksheet in that
formula:

=MID(CELL("filename",a1),FIND("]",CELL("filename",a1),1)+1,32)

otherwise, you may not get what you really expected.

try it in a workbook with two worksheets that are both visible
window|new window
window|arrange

Then watch both those cells when you calculate the workbook.

And the workbook has to be saved (at least once) for this formula to work.



firefytr < said:
hi arunjoshi,

you could also do this with a formula in A1. a couple of options here,
let's look at the following:

with excel's native functions:

=MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,32)

with the Morefunc.xla addin from laurent longre:

=SHEETNAME()

in the first formula, the +1 is used to start at the first letter after
the ] in the filename. the filename is between the [] sign's. the 32
is because a sheet name cannot be longer than that many characters.

i prefer the second formula - for obvious reasons. you can copy/paste
this formula to all sheets, or even run it through a looping macro.

hth
Click to expand...
 
Top