Automatically copy the file name to the worksheet tab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?)
 
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
 
Gord,

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

Bob
 
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
 
Back
Top