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" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1

--

Dave Peterson