Worksheet name

[s]

How do you have an worksheet automatically name itself
from a certain cell in the worksheet?

 

[Frank Kabel]

Hi
you have to use VBA to achieve this. Put the following code in your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This will change the worksheet name to the value in A1

 

[Guest]

So I go to Tools, Macros, Visual Basic Editor ,pasted the
following
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Except I changed A1 to D6, which contained the name I
wanted to use. What am I doing wrong?

 

[Frank Kabel]

Hi
you have to put this in the worksheet module. Try the following
- right click on the tab name of your sheet
- choose 'Code' from the context menu
- Paste the code in the appearing VBA window
- close the VBA editor and save your spreadsheet

 

[Alex]

Hi Frank,

Sorry, that I'm intercepting your conversation.
I need a similar thing. But, I need to get in a cell the
name of a spreadsheet the cell belong to.
I would appreciate if you could clarify how I could do it.

Thanks advance,

Alex

 

[JE McGimpsey]

One way:

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

 

[Frank Kabel]

Hi Alex
enter this in your cell
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("file
name",A1),1))