Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
Is there a way to rename a worksheet based on a cell in the worksheet?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
Running the tiny macro will set the tab name to the value in B9:
Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu201001 "navel151" wrote: Is there a way to rename a worksheet based on a cell in the worksheet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
Great! Now, is there a way to run one macro that would work on multiple
worksheets so that they each get renamed to the value in B9 in that sheet? "Gary''s Student" wrote: Running the tiny macro will set the tab name to the value in B9: Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu201001 "navel151" wrote: Is there a way to rename a worksheet based on a cell in the worksheet? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
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 Right-click on the sheet tab and "View Code". Copy/paste the code into that module. Edit "A1" TO SUIT. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Fri, 19 Feb 2010 23:47:17 -0800, navel151 wrote: Is there a way to rename a worksheet based on a cell in the worksheet? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
Use the code I posted but change it slightly to make it run on all sheets.
Remove the code from the sheet module and place this revision into Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'autoname the worksheet Tab from value in B9 If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B9")) 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 Gord On Sat, 20 Feb 2010 08:19:02 -0800, navel151 wrote: Great! Now, is there a way to run one macro that would work on multiple worksheets so that they each get renamed to the value in B9 in that sheet? "Gary''s Student" wrote: Running the tiny macro will set the tab name to the value in B9: Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu201001 "navel151" wrote: Is there a way to rename a worksheet based on a cell in the worksheet? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
Gord, how do I run this sub? When I put it in the ThisWorkbook module it
doesn't show up in the Maacro list - I would like to have a shortcut key to run it. Thx. "Gord Dibben" wrote: Use the code I posted but change it slightly to make it run on all sheets. Remove the code from the sheet module and place this revision into Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'autoname the worksheet Tab from value in B9 If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B9")) 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 Gord On Sat, 20 Feb 2010 08:19:02 -0800, navel151 wrote: Great! Now, is there a way to run one macro that would work on multiple worksheets so that they each get renamed to the value in B9 in that sheet? "Gary''s Student" wrote: Running the tiny macro will set the tab name to the value in B9: Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu201001 "navel151" wrote: Is there a way to rename a worksheet based on a cell in the worksheet? . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rename Worksheet From Cell Data
It is event code, not a macro to be run via a button or shortcut key.
The code runs on any sheet when you change the value in B9 manually. If you want a macro to run from a shortcut key use the rNmae macro the other responder posted. Gord On Mon, 22 Feb 2010 08:12:01 -0800, navel151 wrote: Gord, how do I run this sub? When I put it in the ThisWorkbook module it doesn't show up in the Maacro list - I would like to have a shortcut key to run it. Thx. "Gord Dibben" wrote: Use the code I posted but change it slightly to make it run on all sheets. Remove the code from the sheet module and place this revision into Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'autoname the worksheet Tab from value in B9 If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B9")) 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 Gord On Sat, 20 Feb 2010 08:19:02 -0800, navel151 wrote: Great! Now, is there a way to run one macro that would work on multiple worksheets so that they each get renamed to the value in B9 in that sheet? "Gary''s Student" wrote: Running the tiny macro will set the tab name to the value in B9: Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu201001 "navel151" wrote: Is there a way to rename a worksheet based on a cell in the worksheet? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rename worksheet based on contents of a cell in different workshee | Excel Discussion (Misc queries) | |||
new worksheet and rename from cell contents macro | Excel Worksheet Functions | |||
Rename a Worksheet on Input of Value in Cell | Excel Worksheet Functions | |||
Insert sheet, move to end, rename with cell data. | Excel Discussion (Misc queries) | |||
how do i rename worksheet to equal cell name | Excel Worksheet Functions |