Automatically copy the file name to the worksheet tab
 

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)

Automatically copy the file name to the worksheet tab
 

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1 or as filename
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = ActiveWorkbook.Name
'if you want value from a cell to be the sheet name
'Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Jul 2007 09:16:03 -0700, Bob wrote:

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)

Automatically copy the file name to the worksheet tab
 

Gord,

thanks for your reply. I'm not sure what to do with this. Where would I
enter this piece of code?

Bob

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1 or as filename
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = ActiveWorkbook.Name
'if you want value from a cell to be the sheet name
'Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Jul 2007 09:16:03 -0700, Bob wrote:

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)

Automatically copy the file name to the worksheet tab
 

Apologies Bob.

This is sheet event code.

Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Edit the "A1" to whatever cell you want to use.

Note the single quote marks before the two lines. They will not run.

I stuck them in there so's you have a choice of a cell value or the workbook.

Remove the quote from 'Me.Name = .Value and add a quote to

Me.Name = ActiveWorkbook.Name

'if you want value from a cell to be the sheet name.....is just a comment.


Gord


On Wed, 11 Jul 2007 17:26:01 -0700, Bob wrote:

Gord,

thanks for your reply. I'm not sure what to do with this. Where would I
enter this piece of code?

Bob

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1 or as filename
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = ActiveWorkbook.Name
'if you want value from a cell to be the sheet name
'Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 11 Jul 2007 09:16:03 -0700, Bob wrote:

How can I automatically insert the file name into the worksheet tab in Excel
2003? Or for that matter, any data in a cell into the tab label? (Without
manually typing it in?)