A list of name's sorted by sheet name

[lord anubis]

Hi, I did put myself over time into a mess, one of my earliest surviving spreadsheet from Excel ( around Excel 5 ) did grow to 37 worksheets beside 12 help sheets. No problem so far.
It was time to do a cleanup. Did refactor some macro's and did get a welcome speedbump. No problems zo far.

However, I got 100+ table names, I guess half are wrong or at-least misplaced ( cell ref ) or for some reason wrongly named and 'Table1234' names that are not in use. Some refer to sheet names that doesn't exists any more for years. I am using Excel for Mac from this year, but many years there where many bugs tat where messing things up. Once I did get an unwanted 50 TablesNames that where created after a kind of recovery. Luckily I could remove them with the Name>DefineName Dialog. Side note, why still no decent Name Manager for Mac, with at least a resize-able dialog.


I know this.

Please Login or Register  to view this content.

But what I want is create a file or sheet with the tablenames sorted by sheetnames
Like

sheet1 - tTable1Name ( referenceToCells )
sheet1 - tTable2 ( referenceToCells )
sheet4 - tTable234 ( referenceCells )
sheet4 - tName ( referenceCells )
Orphans - tTable555 ( referenceCells )
Orphans - tTable654 'Empty reference'

e.t.c

Does someone knows how I can get from a sheet the belonging Name's, and orphaned tTabels/Name's?

Any suggestions.

When I got this neat, then I want to create a macro that creates a sheet from a template and set the Names of the tables to the Sheetname like Product_Fish with table names tProductFishOrderTable and tProductFishPackageTable when copied.

[jolivanes]

to "Dutchland", aka the Netherlands or Nederland.
I still had this hanging around. Does it help at all?
You do need an empty Sheet2 and run it from there.

Please Login or Register  to view this content.

[lord anubis]

Hi Thank you,

I did change a little to your code, because I did get error code 400. I guess I did get this because of some hidden names like
_xlfn.IFERROR
_xlfn.SINGLE
_xlfn.SUMIFS

In the Sample Table there are use of IFERROR and SUM's but no SUMIFs. BTW I thought that this was only in old versions of VBA?
Are those hidden and how should I jump over them cleanly or get a value from those. Now I do a compare with '=NAME..".
Not really needed but curious what they do and can contain.

Please Login or Register  to view this content.

Not sure what in your code the Array wil do, it prints only zero's

BTW, Never thought of this

Please Login or Register  to view this content.

Nice tip

BTW2, If I just check for the names I get 235 Names with

Please Login or Register  to view this content.

I see a lot are created by Excel and never typed in by me. But in the Names>Define Name dialog I see not even half of that number.



Again, Thank you. When I got the basics right I can do more Like adding a kolom/cell for to add a checkmark and then delete the checkmarked one's.

For now I did use it with one Sheet, but can this, the code above, be better? Any suggestions?

Sample is attached.


When I use this on a copy of my original worksheet, I have an issue. How can I solve that?

Please Login or Register  to view this content.

gives me this