Create a list of all named ranges within a sheet 1

[Vamphyri]

I am a TOTAL beginner. Sorry for the simple questions.

I'm trying to figure out how to write the code to have a list generated by copying ALL named ranges within a specific worksheet onto a second sheet.

Amy help would be greatly appreciated.

Thanks Tons!

 

[SkipVought]

Hi,

You don't need code.

On a blank sheet...

Insert/Name/Paste... Paste List

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[SkipVought]

but...

if you insist

    lRow = 1
    For Each n In ActiveWorkbook.Names
        Cells(lRow, 1).Value = n.Name
        Cells(lRow, 2).Value = Right(n.RefersTo, Len(n.RefersTo) - 1)
        lRow = lRow + 1
    Next

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Vamphyri]

Skip,

Thank you. I notice you name is all over the place.

For all of us idiots just starting... Thank you.

Vamphyri

 

[Vamphyri]

How would I bind this list to a combobox?

 

[SkipVought]

Assign to the ListFillRange Property, either the range reference or the NAME of the range.

Alternatively use the AddItem method to load items into the combo.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Vamphyri]

Whenever I use these codes it returns ALL named ranges. Is there any way to return the named ranges within just one worksheet and not for the entire workbook?

 

[SkipVought]

    Dim sSheet, lRow, n
    sSheet = InputBox("Sheet name")
    lRow = 1
    For Each n In ActiveWorkbook.Names
        If Split(Right(n.RefersTo, Len(n.RefersTo) - 1), "!")(0) Like "*" & sSheet & "*" Then
            Cells(lRow, 1).Value = n.Name
            Cells(lRow, 2).Value = Right(n.RefersTo, Len(n.RefersTo) - 1)
            lRow = lRow + 1
        End If
    Next

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}