When I copy a sheet in the workbook, I get a message about a name which
already exists on the destination sheet. I am puzzled. I cannot find that
name in the workbook by listing or doing a search in formulas. There are
several names like that. I had copied a worksheet one time from another
workbook which contained those name. How can I get rid of these range if I do
not find them in my workbook????

Sometimes I inherit workbooks with hidden range names that cause the same
problems.

Drop this code into a General Module, then go to any sheet and run the
ListActShtRngNames macro:

Sub ListActShtRngNames()
Dim strText As String
Dim intNameCount As Integer
Dim intCtr As Integer
Dim blnMakeVisible As Boolean

strText = ""

Select Case MsgBox( _
Title:="Hidden Range Name Option", _
Prompt:="Do you want to force all hidden range names to be made
visible?", _
Buttons:=vbCritical + vbYesNoCancel)

Case vbYes:
blnMakeVisible = True
Case vbNo:
blnMakeVisible = False
Case vbCancel
Exit Sub
End Select

With ActiveWorkbook
intNameCount = .Names.Count
For intCtr = 1 To intNameCount
strText = strText & Names(intCtr).Name & " " _
& Names(intCtr).Visible & " " _
& Names(intCtr).RefersTo & vbCr

If Names(intCtr).Visible = False Then
Names(intCtr).Visible = blnMakeVisible
End If

Next intCtr

End With
MsgBox strText

End Sub

Note: you may need to run it on each sheet if there are Sheet level names.

I hope that helps.
--
Regards,
Ron

I KEEP GETTING COMPILE ERROR: Sub or Function not defined.
I inserted this code into my Module1
What am I missing??
I am trying to run it from Tool-Macro-Macros-Run
Please help!


"Ron Coderre" wrote:

Sometimes I inherit workbooks with hidden range names that cause the same
problems.

Drop this code into a General Module, then go to any sheet and run the
ListActShtRngNames macro:

Sub ListActShtRngNames()
Dim strText As String
Dim intNameCount As Integer
Dim intCtr As Integer
Dim blnMakeVisible As Boolean

strText = ""

Select Case MsgBox( _
Title:="Hidden Range Name Option", _
Prompt:="Do you want to force all hidden range names to be made
visible?", _
Buttons:=vbCritical + vbYesNoCancel)

Case vbYes:
blnMakeVisible = True
Case vbNo:
blnMakeVisible = False
Case vbCancel
Exit Sub
End Select

With ActiveWorkbook
intNameCount = .Names.Count
For intCtr = 1 To intNameCount
strText = strText & Names(intCtr).Name & " " _
& Names(intCtr).Visible & " " _
& Names(intCtr).RefersTo & vbCr

If Names(intCtr).Visible = False Then
Names(intCtr).Visible = blnMakeVisible
End If

Next intCtr

End With
MsgBox strText

End Sub

Note: you may need to run it on each sheet if there are Sheet level names.

I hope that helps.
--
Regards,
Ron

It looks like you need to get rid of the line break in the Prompt text
between the words "made" and "visible" (see below). They should be on the
same line.

I think that will fix it for you.
--
Regards,
Ron


Select Case MsgBox( _
Title:="Hidden Range Name Option", _
Prompt:="Do you want to force all hidden range names to be made
visible?", _
Buttons:=vbCritical + vbYesNoCancel)

I had put that whole statement on one line!!

"Ron Coderre" wrote:

It looks like you need to get rid of the line break in the Prompt text
between the words "made" and "visible" (see below). They should be on the
same line.

I think that will fix it for you.
--
Regards,
Ron


Select Case MsgBox( _
Title:="Hidden Range Name Option", _
Prompt:="Do you want to force all hidden range names to be made
visible?", _
Buttons:=vbCritical + vbYesNoCancel)

Hmmm...Now I'm really curious.
I copied the code you (re)posted into a new VBA module in a new workbook,
removed the "" characters from it, and the only error I got was the broken
line. Where in the code is the error being highlighted?

--
Regards,
Ron

If you're going to work with names, get a copy of Jan Karel Pieterse's (with
Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

Firebird wrote:

When I copy a sheet in the workbook, I get a message about a name which
already exists on the destination sheet. I am puzzled. I cannot find that
name in the workbook by listing or doing a search in formulas. There are
several names like that. I had copied a worksheet one time from another
workbook which contained those name. How can I get rid of these range if I do
not find them in my workbook????

--

Dave Peterson

Thank you Dave, I will check the Name Manager. Righ now I am trying the code
that Ron Coderre has sugested but I am getting compile error.

"Dave Peterson" wrote:

If you're going to work with names, get a copy of Jan Karel Pieterse's (with
Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

Firebird wrote:

When I copy a sheet in the workbook, I get a message about a name which
already exists on the destination sheet. I am puzzled. I cannot find that
name in the workbook by listing or doing a search in formulas. There are
several names like that. I had copied a worksheet one time from another
workbook which contained those name. How can I get rid of these range if I do
not find them in my workbook????

--

Dave Peterson