VBA to name worksheet
 

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


--
Kimberly
------------------------------------------------------------------------
Kimberly's Profile: http://www.excelforum.com/member.php...fo&userid=7324
View this thread: http://www.excelforum.com/showthread...hreadid=488858

VBA to name worksheet
 

right click sheet tabview codeleft window select worksheetright window
select worksheet_change

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


--
Don Guillett
SalesAid Software

"Kimberly" wrote in
message ...

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


--
Kimberly
------------------------------------------------------------------------
Kimberly's Profile:
http://www.excelforum.com/member.php...fo&userid=7324
View this thread: http://www.excelforum.com/showthread...hreadid=488858

VBA to name worksheet
 

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

VBA to name worksheet
 

I will try it. Kimberl

--
Kimberl
-----------------------------------------------------------------------
Kimberly's Profile: http://www.excelforum.com/member.php...nfo&userid=732
View this thread: http://www.excelforum.com/showthread.php?threadid=48885

VBA to name worksheet
 

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"

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
right click sheet tabview codeleft window select worksheetright window
select worksheet_change

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


--
Don Guillett
SalesAid Software

"Kimberly" wrote
in message ...

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


--
Kimberly
------------------------------------------------------------------------
Kimberly's Profile:
http://www.excelforum.com/member.php...fo&userid=7324
View this thread:
http://www.excelforum.com/showthread...hreadid=488858

VBA to name worksheet
 

It works beautifully.
Thank you.
Kimberly


--
Kimberly
------------------------------------------------------------------------
Kimberly's Profile: http://www.excelforum.com/member.php...fo&userid=7324
View this thread: http://www.excelforum.com/showthread...hreadid=488858

VBA to name worksheet
 

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

--
Don Guillett
SalesAid Software

"Kimberly" wrote in
message ...

It works beautifully.
Thank you.
Kimberly


--
Kimberly
------------------------------------------------------------------------
Kimberly's Profile:
http://www.excelforum.com/member.php...fo&userid=7324
View this thread: http://www.excelforum.com/showthread...hreadid=488858