Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |