Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

Dim nme As Name
Dim i As Long

For Each nem In ActiveWorkbook.Names
i = i + 1
Cells(i, "A").Value = nme.Name
Cells(i, "B").Value = nme.RefersTo
Next i


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

Bob, you forgot something

Dim nme As Name
Dim i As Long

i = 1
Cells(i,"A").value = "Name"
Cells(i,"B").value = "RefersTo"
For Each nme In ActiveWorkbook.Names
i = i + 1
Cells(i, "A").Value = nme.Name
Cells(i, "B").Value = nme.RefersTo
Next nme

:)

Barb Reinhardt
"Bob Phillips" wrote:

Dim nme As Name
Dim i As Long

For Each nem In ActiveWorkbook.Names
i = i + 1
Cells(i, "A").Value = nme.Name
Cells(i, "B").Value = nme.RefersTo
Next i


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

Chip Pearson wrote:
Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.

Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks


Thanks Chip, Very useful

--
Message posted via http://www.officekb.com

Chip: Good Post. What if you want to return a subset of the range names?

I have a workbook that has about 100 names in it. I need to extract a
subset, say all names beginning with "INPUT_", and use them in a loop to
clear data or autofill.

Is this possible?

"Chip Pearson" wrote:

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

Try something like


Sub AAA()

Dim NM As Name
For Each NM In ThisWorkbook.Names
If StrComp(Left(NM.Name, Len("INPUT_")), "INPUT_", vbTextCompare) = 0
Then
' name begins with "INPUT_". Do something with NM
End If
Next NM

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"OldAuditor" wrote in message
...
Chip: Good Post. What if you want to return a subset of the range names?

I have a workbook that has about 100 names in it. I need to extract a
subset, say all names beginning with "INPUT_", and use them in a loop to
clear data or autofill.

Is this possible?

"Chip Pearson" wrote:

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks

Chip's method is good: or if you are doing much work with defined names you
can download Name Manager from
http://www.decisionmodels.com/downloads.htm

This free addin allows you to filter names by many different criteria,
You can also list the filtered names on a worksheet, edit them and then
re-import them into one or more workbooks


Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Chip Pearson" wrote in message
...
Try something like


Sub AAA()

Dim NM As Name
For Each NM In ThisWorkbook.Names
If StrComp(Left(NM.Name, Len("INPUT_")), "INPUT_", vbTextCompare) = 0
Then
' name begins with "INPUT_". Do something with NM
End If
Next NM

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"OldAuditor" wrote in message
...
Chip: Good Post. What if you want to return a subset of the range names?

I have a workbook that has about 100 names in it. I need to extract a
subset, say all names beginning with "INPUT_", and use them in a loop to
clear data or autofill.

Is this possible?

"Chip Pearson" wrote:

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in
a
worksheet and list them in a worksheet?

thanks

Chip: Thanks for the quick response.

I will try it and let you know how it works.

Tom

"Chip Pearson" wrote:

Try something like


Sub AAA()

Dim NM As Name
For Each NM In ThisWorkbook.Names
If StrComp(Left(NM.Name, Len("INPUT_")), "INPUT_", vbTextCompare) = 0
Then
' name begins with "INPUT_". Do something with NM
End If
Next NM

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"OldAuditor" wrote in message
...
Chip: Good Post. What if you want to return a subset of the range names?

I have a workbook that has about 100 names in it. I need to extract a
subset, say all names beginning with "INPUT_", and use them in a loop to
clear data or autofill.

Is this possible?

"Chip Pearson" wrote:

Bret,

Try:

Range("A1").ListNames

A1 is where the list of names is to begin.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Bret" wrote in message
...
Is there a way to programmatically capture all the 'defined names' in a
worksheet and list them in a worksheet?

thanks