Automatically update worksheet name

  • Thread starter Thread starter JuneS
  • Start date Start date
J

JuneS

Is there a way to change the names of worksheets automatically? I use Excel
2007 for invoicing, but it's really a pain to have to rename each sheet to
reflect my invoice number.

Is there a formula or solution for this?

Thanks!
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '<== change to cell with invoice number

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
 
Assuming the Invoice Number is located in cell F4 on each and every sheet and
its format/shape/size/used characters is "legitimate" for a sheets name -
put the following Event-Macro into the ThisWorkBook Module:
 
Back
Top