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

On Mon, 28 Nov 2005 14:41:14 -0600, Kimberly
wrote:


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


--
Regards
Frank Kabel
Frankfurt, Germany