Create List of Range Names by Sheet on a Second Sheet

[Vamphyri]

Happy Friday!!!

Is it possible to create a list of Range Names that is broken out by Sheet? ie..

Sheet 1 Names Sheet 2 Names Sheet 3 Names
Range Name S1-1 Range Name S2-1 Range Name S3-1
Range Name S1-2 Range Name S2-2 Range Name S3-2
Range Name S1-3 Range Name S3-3
Range Name S1-4

Many a "pro-active" thanks for any help.


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

Hi,

Check out Insert/Name/Paste - Paste All

Skip,

_[red][/red]

 

[Vamphyri]

Skip,

Thank you for your tip. What I am looking to do is have the Range Names from Sheet 1 in column B, Range Names from Sheet 2 in column C, etc.

When I use your tip I get a complete list, which is great, but I need to have it separated by Sheet Name into different columns.

Is this possible?

Thanks again


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

sort, cut 'n' paste

Skip,

_[red][/red]

 

[SkipVought]

Programatically???

lrow = 1
for each nm in activeworkbook.names
  sSheet = right(split(nm,"!")(0), len(split(nm,"!")(0), )-1)sRef = split(nm,"!")(1)
   icol = 1
   for each sh in worksheets
      if sh.name = sname then
         cells(lrow, icol).value = sname & "!" & sref
         exit for
      end if
      icol = icol + 1
   next
end select
lrow = lrow + 1
next

will split out the sheet and reference.



Skip,

_[red][/red]

 

[SkipVought]

correction...

    lrow = 1
    For Each nm In ActiveWorkbook.Names
       ssheet = Right(Split(nm, "!")(0), Len(Split(nm, "!")(0)) - 1)
       sRef = Split(nm, "!")(1)
       icol = 1
       For Each sh In Worksheets
          If sh.Name = ssheet Then
             ActiveSheet.Cells(lrow, icol).Value = nm.Name & " " & sRef
             Exit For
          End If
          icol = icol + 1
       Next
       lrow = lrow + 1
    Next

Skip,

_[red][/red]

 

[Vamphyri]

Skip,

I placed your code into a Sub and ran it. Nothing happened. I didn't get any errors, which is rather unusual for me, but still nothing.

What am I doing wrong?

Thanks


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

Active sheet should be empty when you run.

Results appear on that sheet.

Skip,

_[red][/red]

 

[SkipVought]

...in the same workbook you are analyzing, I should add.

Skip,

_[red][/red]

 

[Vamphyri]

Skip,

Gotcha.

I was trying to use your code in a WS that already had some info on it.

I deleted all data from the WS and reran the code.

It brings up only 1 WS name and no Range Names (I have approximately 80-90).

The weird thing is the code places its only entry in cell B12.

I can only assume I have completely screwed something up.

Any ideas?


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

Use the SECOND code I posted.

Skip,

_[red][/red]

 

[Vamphyri]

Skip

I am using the code you posted at 13:47 that starts with
"correction".


Sub MakeRangeNameListBySheet()
lrow = 1
For Each nm In ActiveWorkbook.Names
ssheet = Right(Split(nm, "!")(0), Len(Split(nm, "!")(0)) - 1)
sRef = Split(nm, "!")(1)
icol = 1
For Each sh In Worksheets
If sh.Name = ssheet Then
ActiveSheet.Cells(lrow, icol).Value = nm.Name & " " & sRef
Exit For
End If
icol = icol + 1
Next
lrow = lrow + 1
Next

End Sub

Is this right?


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

yes, and....

Skip,

_[red][/red]

 

[SkipVought]

If you have SHEET LEVEL NAMES (same range name on more than one sheet) you will need to do something else as well.

Do YOU?

Skip,

_[red][/red]

 

[Vamphyri]

No sir!

All Range Names are unique. No dupes.

What now? (he asks sheepishly).


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

Hmmmmm???

When you do

Insert/Name/Define

what do you see: your 80 names?

Skip,

_[red][/red]

 

[Vamphyri]

Skip,

Yes. However, I WAY underestimated the number of Range Names I have. There are 279 of the little suckers.

The list comes out in 2 columns. Caolumn A is the Range Name itself and column B is the referrence(including the Sheet Name).

Does this help?


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

where they are is of no consequence to me.

describe EXACTLY what you see in DEFINE for the FIRST entry.

Skip,

_[red][/red]

 

[Vamphyri]

Column A Entry

AGC_HNO_A15_2000_2_2000_2006

Column B Entry

='AGC HNO Plans 2006'!$O$2:$Q$39


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

I did not ask what was in columns A & B.

I asked what is in DEFINE, when you SELECT the first entry, what's in the NAME box and what's in the RefersTo Box?

Skip,

_[red][/red]