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.

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.

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.

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.

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.

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.

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.

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.

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.