VBA to name worksheet

[Kimberly]

Some time ago, I posted the inquiry below. Frank, the Excel guru,
helped me with the VBA programming. However, I would like this to run
automatically when a change is made in the contents of cell A1 (using
Frank's reply example). So, when A1 changes, the name of the page
automatically changes also.
Any ideas?
Thanks!
Kimberly

In order to save time with dozens of identical worksheets bearing a
similar name, I am trying to find a function that would remain in a
cell that based upon one set of input criteria would insert a
descriptor name into the worksheet name.
For instance, I have 4 pages tracking production costs on 4 widgets.
Costs are all the same, but at different levels. Using the indirect
search function and vlookup, I can quickly update each widgets sheet
from a master list of costs. However, I am tired of having to rename
each sheet and the production name on it for each line with each new
product group.
I would like to maintain one list (with columns widget id #, widget
name, widget line, widget cost, etc). I would like to be able to just
copy my production cost tracking page, input the widget id #, and the
rest would feet automatically. All of that is not a problem with the
exception of the WORKSHEET NAME. Can anyone please help me pull the
widget name into the worksheet name. Like:
Widget name: Mongo
Worksheet name: Mongo production worksheet
Thanks!!! Kimberly

Hi
this can only be done with VBA.
e.g.
sub foo()
activesheet.name = activesheet.range("A1").value _
& " production worksheet"
end sub

if A1 sotres your worksheet name

 

[Don Guillett]

right click sheet tab>view code>left window select worksheet>right window
select worksheet_change

use this code
if target.address= "$A$1" then
activesheet.name = target 'activesheet.range("A1").value _
& " production worksheet"
end sub

 

[Gord Dibben]

Kimberly

Private Sub Worksheet_Change(ByVal Target As Range)
''autoname the worksheet Tab from value in A1
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 = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right-click on the sheet tab and "View Code".

Paste into that module.


Gord Dibben Excel MVP

 

[Kimberly]

I will try it. Kimberl

 

[Don Guillett]

I forgot an end if so use this instead of the end if

If Target.Address <> "$A$1" Then Exit Sub
ActiveSheet.Name = Target & " production worksheet"

 

[Kimberly]

It works beautifully.
Thank you.
Kimberly

 

[Don Guillett]

glad it helped. Only too bad that Frank is no longer with us and able to
help