Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
I have a workbook with 75 worksheets (tabs) and would like the value in field
D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Not Intersect(Range("D1"), Target) Is Nothing Then Sh.Name = Target.Value End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
To run across all sheets:
Sub SheetNamesD1() Dim wks As Worksheet For Each wks In Worksheets wks.Activate wks.Name = Range("D1").Value Next wks End Sub "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
Thank. I found where to enter the code below, but now what. I click on save
while in the view code area and nothing is happening to the tab names. "Bob Phillips" wrote: 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Not Intersect(Range("D1"), Target) Is Nothing Then Sh.Name = Target.Value End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
Note that with both Bob's and PCLives code you could run into an issue if the
value in D1 is the repeat of an existing tab name or if the text in D1 is too long, or if the text in D1 is "history" (quirky little bug with XL that you can not name a sheet History)... If that is going to be an issue then you should add in an error handler to the code which we can help you with if you need... -- HTH... Jim Thomlinson "Bob Phillips" wrote: 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Not Intersect(Range("D1"), Target) Is Nothing Then Sh.Name = Target.Value End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
Is this a Macro I should record or should it also be in the View Code area?
Either way....what do I do once I put the there or create the macro? "PCLIVE" wrote: To run across all sheets: Sub SheetNamesD1() Dim wks As Worksheet For Each wks In Worksheets wks.Activate wks.Name = Range("D1").Value Next wks End Sub "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
OK...Do I create a macro for the code below or do I put it in the View Code
area like the other help thread? Either way, what do I do once I put it code where it belongs to make it happen? "PCLIVE" wrote: To run across all sheets: Sub SheetNamesD1() Dim wks As Worksheet For Each wks In Worksheets wks.Activate wks.Name = Range("D1").Value Next wks End Sub "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
Paste this code where the other code was is fine. Place your cursor in the
middle of the code and then hit the F5 key to run the code... All of the tab names will change... You can now delete this code. Bob's code is event driven. If you select cell D1 and then exit the cell it will change the tab name to the value in D1. So if you change the value in D1 the tab name will always stay in sync. Note to Bob... Why selection change and not Change? -- HTH... Jim Thomlinson "Michelle" wrote: OK...Do I create a macro for the code below or do I put it in the View Code area like the other help thread? Either way, what do I do once I put it code where it belongs to make it happen? "PCLIVE" wrote: To run across all sheets: Sub SheetNamesD1() Dim wks As Worksheet For Each wks In Worksheets wks.Activate wks.Name = Range("D1").Value Next wks End Sub "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tab Naming
If it helps anything, I was going to reply:
Sub Macro1() Dim sheetname, cellvalue As String Range("D1").Select cellvalue = ActiveCell.FormulaR1C1 ' The value inside the cell sheetname = ActiveSheet.Name ' The sheet's current name ActiveSheet.Select ' Activate the current sheet's name for editing Sheets(sheetname).Name = cellvalue ' Edit the current sheet's name Range("A1").Select ' De-select the current sheet's name, so you can move on ActiveSheet.Next.Select ' Select the worsheet to the right. End Sub "Jim Thomlinson" wrote: Paste this code where the other code was is fine. Place your cursor in the middle of the code and then hit the F5 key to run the code... All of the tab names will change... You can now delete this code. Bob's code is event driven. If you select cell D1 and then exit the cell it will change the tab name to the value in D1. So if you change the value in D1 the tab name will always stay in sync. Note to Bob... Why selection change and not Change? -- HTH... Jim Thomlinson "Michelle" wrote: OK...Do I create a macro for the code below or do I put it in the View Code area like the other help thread? Either way, what do I do once I put it code where it belongs to make it happen? "PCLIVE" wrote: To run across all sheets: Sub SheetNamesD1() Dim wks As Worksheet For Each wks In Worksheets wks.Activate wks.Name = Range("D1").Value Next wks End Sub "Michelle" wrote in message ... I have a workbook with 75 worksheets (tabs) and would like the value in field D1 to be the name of the tab? Is this possible and how would I do this? Please help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Cell in diffetn worksheet | Excel Worksheet Functions | |||
intersection naming transport between files | Excel Discussion (Misc queries) | |||
Tab naming | Excel Discussion (Misc queries) | |||
E2003 naming ranges correctly | Excel Discussion (Misc queries) | |||
Naming ranges? | Excel Discussion (Misc queries) |