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" <(E-Mail Removed)> wrote in message
news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:38E0BC2A-7940-47A2-871F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:ED8B5B80-3C71-4560-983C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:38E0BC2A-7940-47A2-871F-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
> news:38E0BC2A-7940-47A2-871F-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:6799B669-DE06-4886-81F1-(E-Mail Removed)...
> >> > Is there a way to programmatically capture all the 'defined names' in a
> >> > worksheet and list them in a worksheet?
> >> >
> >> > thanks
> >>
>