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

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

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

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

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

It highlights at the Sub line

"Ron Coderre" wrote:

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

Something's happening in your situation that I can't replicate....So...Try
this:

1)Open a new workbook
2)Open the VBE [Alt]+[F11]
3)Insert a new Module
4)Copy the code below into that module:

Option Explicit
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

5)Compile the project DebugCompile VBA Project

6)No errors, except for the wrapped line (I hope). If you run into problems,
let me know what they are, OK?

Otherwise:
7)Go to the workbook and run the code:
ToolsMacroMacrosListActShtRngNames

It should run (even without any range names). Then try it with range names.

NOTE: To run it on another workbook, the workbook with the code has to be
open, too. OR you could put it in your PERSONAL.XLS workbook so it would
always be available.

Waiting to hear from you . . .

--
Regards,
Ron

I will try your suggestion. Will let you know

"Ron Coderre" wrote:

Something's happening in your situation that I can't replicate....So...Try
this:

1)Open a new workbook
2)Open the VBE [Alt]+[F11]
3)Insert a new Module
4)Copy the code below into that module:

Option Explicit
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

5)Compile the project DebugCompile VBA Project

6)No errors, except for the wrapped line (I hope). If you run into problems,
let me know what they are, OK?

Otherwise:
7)Go to the workbook and run the code:
ToolsMacroMacrosListActShtRngNames

It should run (even without any range names). Then try it with range names.

NOTE: To run it on another workbook, the workbook with the code has to be
open, too. OR you could put it in your PERSONAL.XLS workbook so it would
always be available.

Waiting to hear from you . . .

--
Regards,
Ron

What I did was that instead of copying the code like the last time, I
manually typed in with a different macro name in the same module and it
worked. I do like to find out what invisible characters got copied within the
code which were causing problem.
Any how I ran the code on all worksheets with identical results. I did find
some names but not all the ones that I encountered when I was copying the
sheet.
You have been great and I really appreciate your help in resolving the
problem.

Sincerely,


"Firebird" wrote:

I will try your suggestion. Will let you know

"Ron Coderre" wrote:

Something's happening in your situation that I can't replicate....So...Try
this:

1)Open a new workbook
2)Open the VBE [Alt]+[F11]
3)Insert a new Module
4)Copy the code below into that module:

Option Explicit
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

5)Compile the project DebugCompile VBA Project

6)No errors, except for the wrapped line (I hope). If you run into problems,
let me know what they are, OK?

Otherwise:
7)Go to the workbook and run the code:
ToolsMacroMacrosListActShtRngNames

It should run (even without any range names). Then try it with range names.

NOTE: To run it on another workbook, the workbook with the code has to be
open, too. OR you could put it in your PERSONAL.XLS workbook so it would
always be available.

Waiting to hear from you . . .

--
Regards,
Ron

I jumped the gun when I said it found some. When I did
Insert-Name-Paste-Past List
All were there.
I am high in the sky!!!
Thanks a bunch.

"Firebird" wrote:

What I did was that instead of copying the code like the last time, I
manually typed in with a different macro name in the same module and it
worked. I do like to find out what invisible characters got copied within the
code which were causing problem.
Any how I ran the code on all worksheets with identical results. I did find
some names but not all the ones that I encountered when I was copying the
sheet.
You have been great and I really appreciate your help in resolving the
problem.

Sincerely,


"Firebird" wrote:

I will try your suggestion. Will let you know

"Ron Coderre" wrote:

Something's happening in your situation that I can't replicate....So...Try
this:

1)Open a new workbook
2)Open the VBE [Alt]+[F11]
3)Insert a new Module
4)Copy the code below into that module:

Option Explicit
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

5)Compile the project DebugCompile VBA Project

6)No errors, except for the wrapped line (I hope). If you run into problems,
let me know what they are, OK?

Otherwise:
7)Go to the workbook and run the code:
ToolsMacroMacrosListActShtRngNames

It should run (even without any range names). Then try it with range names.

NOTE: To run it on another workbook, the workbook with the code has to be
open, too. OR you could put it in your PERSONAL.XLS workbook so it would
always be available.

Waiting to hear from you . . .

--
Regards,
Ron

Glad to hear it!

I was kinda hoping you'd come back with something like that because that
code hasn't failed me before.

--
Regards,
Ron

Ron,

Thanks for the code. I have used VBE twice I don't have a clue what it is
or does but when I ran the Macro the names were listed and I could delete
them. Thanks again.


"Ron Coderre" wrote:

Glad to hear it!

I was kinda hoping you'd come back with something like that because that
code hasn't failed me before.

--
Regards,
Ron