Cell A1 to sheet name

[Bill_Green via OfficeKB.com]

I am trying to make 87 sheets in a workbook change their name to reflect that
of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe
first one and here's the code I've entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

I just right clicked the tab, clicked "view code" and inserted this (it's
from a previous post. I'm a novice, but am working on it). Assuming this can
be made to work, is there a way to make this loop through all the sheets
instead of doing this one at a time? This will be a monthly task.

 

[Rick Rothstein \(MVP - VB\)]

Since you can't name each sheet the same thing, you will need to tell us how
you want to apply what is in B1 to the existing sheet names (concatenate it
on to the beginning or end, replace some existing text, something else
entirely?).

Rick

 

[Dave Peterson]

How many worksheets are in this workbook--87? (Any worksheets to avoid???)

Do you want each worksheet named after what's found in B1 of that particular
worksheet?

Do the values in B1 change often?

If yes, then you could use a workbook event (instead of 87 separat worksheet
events) -- but that would depend on knowing the names of the sheets to be
avoided, too.

If no, then you could use a macro that would cycle through the worksheets and
change the names accordingly--and if the values in B1 change, you could just run
the macro when you wanted to. You wouldn't need excel to always be looking for
a change.

 

[Bill_Green via OfficeKB.com]

I was able to get it to apply to all sheets, and yes it needs to pull from B1
on every sheet. Can I get it to hit only the selected sheets? Here's the code
I have now:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/4/2008 by bgreen
Dim sh As Worksheet
For i = 1 To Sheets.Count
Set sh = Sheets(i)
sh.Activate

With ActiveSheet
.Name = .Range("B1").Value
End With

Next i
End Sub

The actual name of the product is in cell A1, but it's very long and the
first 15 digits are the same or close to it on most of them, so I selected
the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to
get past the digits that are the same and since the name of the tab has to be
less than 31 characters long, then copied and pasted special "values." This
is a lot of steps.

How many worksheets are in this workbook--87? (Any worksheets to avoid???)

Do you want each worksheet named after what's found in B1 of that particular
worksheet?

Do the values in B1 change often?

If yes, then you could use a workbook event (instead of 87 separat worksheet
events) -- but that would depend on knowing the names of the sheets to be
avoided, too.

If no, then you could use a macro that would cycle through the worksheets and
change the names accordingly--and if the values in B1 change, you could just run
the macro when you wanted to. You wouldn't need excel to always be looking for
a change.

I am trying to make 87 sheets in a workbook change their name to reflect that
of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe

[quoted text clipped - 21 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200808/1

 

[Bill_Green via OfficeKB.com]

By the way, the code is in a regular macro now, not the sheet coding (I can't
make that work anyway).

I was able to get it to apply to all sheets, and yes it needs to pull from B1
on every sheet. Can I get it to hit only the selected sheets? Here's the code
I have now:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/4/2008 by bgreen
Dim sh As Worksheet
For i = 1 To Sheets.Count
Set sh = Sheets(i)
sh.Activate

With ActiveSheet
.Name = .Range("B1").Value
End With

Next i
End Sub

The actual name of the product is in cell A1, but it's very long and the
first 15 digits are the same or close to it on most of them, so I selected
the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to
get past the digits that are the same and since the name of the tab has to be
less than 31 characters long, then copied and pasted special "values." This
is a lot of steps.

How many worksheets are in this workbook--87? (Any worksheets to avoid???)

[quoted text clipped - 17 lines]

 

[Rick Rothstein \(MVP - VB\)]

Give this macro a try...

Sub ChangeNameOnSelectedSheets()
Dim SH As Worksheet
For Each SH In ActiveWindow.SelectedSheets
SH.Name = SH.Range("B1").Value
Next
End Sub

Rick

I was able to get it to apply to all sheets, and yes it needs to pull from
B1
on every sheet. Can I get it to hit only the selected sheets? Here's the
code
I have now:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/4/2008 by bgreen
Dim sh As Worksheet
For i = 1 To Sheets.Count
Set sh = Sheets(i)
sh.Activate

With ActiveSheet
.Name = .Range("B1").Value
End With

Next i
End Sub

The actual name of the product is in cell A1, but it's very long and the
first 15 digits are the same or close to it on most of them, so I selected
the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to
get past the digits that are the same and since the name of the tab has to
be
less than 31 characters long, then copied and pasted special "values."
This
is a lot of steps.

How many worksheets are in this workbook--87? (Any worksheets to
avoid???)

Do you want each worksheet named after what's found in B1 of that
particular
worksheet?

Do the values in B1 change often?

If yes, then you could use a workbook event (instead of 87 separat
worksheet
events) -- but that would depend on knowing the names of the sheets to be
avoided, too.

If no, then you could use a macro that would cycle through the worksheets
and
change the names accordingly--and if the values in B1 change, you could
just run
the macro when you wanted to. You wouldn't need excel to always be
looking for
a change.

I am trying to make 87 sheets in a workbook change their name to reflect
that
of what is in cell B1. Is there a way to do that in VBA? I've tried it
onthe

[quoted text clipped - 21 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200808/1

 

[Bill_Green via OfficeKB.com]

Looks like that will work. Thanks! I appreciate it.

Give this macro a try...

Sub ChangeNameOnSelectedSheets()
Dim SH As Worksheet
For Each SH In ActiveWindow.SelectedSheets
SH.Name = SH.Range("B1").Value
Next
End Sub

Rick

I was able to get it to apply to all sheets, and yes it needs to pull from
B1

[quoted text clipped - 56 lines]

 

[Dave Peterson]

If you want to name all the worksheets based on what's in A1 (you could drop the
formula in B1):

Option Explicit
Sub Macro2A()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = Mid(sh.Range("a1").Value, 16, 31)
If Err.Number <> 0 Then
Err.Clear
MsgBox "Sheet: " & sh.Name & vbLf & "wasn't renamed"
End If
On Error GoTo 0
Next sh
End Sub

By the way, the code is in a regular macro now, not the sheet coding (I can't
make that work anyway).

I was able to get it to apply to all sheets, and yes it needs to pull from B1
on every sheet. Can I get it to hit only the selected sheets? Here's the code
I have now:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/4/2008 by bgreen
Dim sh As Worksheet
For i = 1 To Sheets.Count
Set sh = Sheets(i)
sh.Activate

With ActiveSheet
.Name = .Range("B1").Value
End With

Next i
End Sub

The actual name of the product is in cell A1, but it's very long and the
first 15 digits are the same or close to it on most of them, so I selected
the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to
get past the digits that are the same and since the name of the tab has to be
less than 31 characters long, then copied and pasted special "values." This
is a lot of steps.

How many worksheets are in this workbook--87? (Any worksheets to avoid???)

[quoted text clipped - 17 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200808/1