Copy of sheet contains the named 'Control'. Action required.

[Pete]

Hi to all

I have a macro that copies a sheet and although it was working well, one day
I suddenly got the message:

A formula or sheet you want to move or copy contains the name
'HTML_Control', which already exists on the destination worksheet. Do want
to use this version of the name?
To use the name . . . . etc


If you click on 'Yes' it continues an if 'No' you can't continue although it
doesn't actually 'bomb'.

Although it still works, the user gets very confused each time it happens
(each month).

What causes it and how can I find the named 'Control' and eliminate it?
I have searched for 'HTML_Control' without success.

Thanks
Peter Bircher

 

[Pete]

Asked and answered . . .

I have just been on to the Microsoft support site (yes, I know I sould have
looked there first but , , , ) and found this program and it worked like a
charm so I will paste it as is:

' Module to remove all hidden names on active workbook
Sub Remove_Hidden_Names()

' Dimension variables.
Dim xName As Variant
Dim Result As Variant
Dim Vis As Variant

' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names

'If a name is not visible (it is hidden)...
If xName.Visible = True Then
Vis = "Visible"
Else
Vis = "Hidden"
End If

' ...ask whether or not to delete the name.
Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
Chr(10) & xName.Name & "?" & Chr(10) & _
"Which refers to: " & Chr(10) & xName.RefersTo, _
Buttons:=vbYesNo)

' If the result is true, then delete the name.
If Result = vbYes Then xName.Delete

' Loop to the next name.
Next xName

End Sub