How do I delete all ranges named "startX" where X is the index number
for a worksheet in the active workbook?

This is a non-elegant solution to the following problem. Surely someone
has an elegant solution ...

I am using the following code to create an index (table of contents) of
worksheets in the active workbook. Another procedure creates a new
worksheet for a new customer by copying the "New Customer" worksheet
with the new customer's name as the worksheet name. When this
create-a-new-customer procedure runs, this ugly-but-understandable
error occurs:

A formula or sheet you want to move or copy contains the name
'wrn.Q1.', which already exists on the destination worksheet. Do you
want to use this version of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formula or worksheet, click
No, and enter a new name in the Name Conflict dialog box.

Since the person who commissioned this "simple" project is a pure
novice, I'm trying to circumvent unnecessarily confusing situations.
Since the informational dialog box should always be clicked Yes, I want
to either find a way not to display it or remove the cause.

In any case here's code (as somewhat modified from a Web page).
Apologies in advance for the lack of formatting:

Private Sub Worksheet_Activate()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim wSheetIndex As Long
Dim M As Long
M = 1
With Me
..Columns(1).ClearContents
..Cells(1, 1) = "Customer Index"
..Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
ActiveSheet.Calculate
If wSheet.Name < Me.Name Then

' Don't want an index entry for menu sheet
If wSheet.Name < "MenuSheet" Then
' Don't want an index entry for New Customer template
sheet
If wSheet.Name < "New Customer" Then
M = M + 2
With wSheet
..Range("A1").Name = "Start" & wSheet.Index
..Hyperlinks.Add Anchor:=.Range("B1:C1"),
Address:="", _
SubAddress:="Index", TextToDisplay:="Return
to Index"
With .Cells.Range("B1:C1")
..Merge
..Interior.ColorIndex = 6
..Interior.Pattern = xlSolid
..HorizontalAlignment = xlCenter
..VerticalAlignment = xlCenter
..Font.Bold = True
End With
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1),
Address:="", _
SubAddress:="Start" & wSheet.Index, _
TextToDisplay:=wSheet.Name
End If
End If
End If
Next wSheet
ActiveSheet.Range("A1").Select
End Sub

Any help and suggestions would be greatly appreciated.

- Al (aka "Go Figure")


--
GoFigure
------------------------------------------------------------------------
GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274
View this thread: http://www.excelforum.com/showthread...hreadid=490463