There are times where you may have a large number of named ranges in a workbook. I like to have a general rule when creating a workbook. No more than half a dozen named ranges. Formula auditing turns into a nightmare if there are too many named ranges. You may well know what CapitalExpense refers to when you create the named range but as your file gets 20-30 tabs and a dozen other named ranges, finding CapitalExpense can prove time consuming if you have to go to the name manager continually to tell you the location of the named range. The following is a simple piece of code which will identify all of the named ranges in a file and show what range the named ranges refer to.
Option Explicit
Sub WkbNames() 'Show all named Ranges in Sheet1.
Dim sh As Worksheet
Dim nm As Name
Set sh=Sheet1 'Ensure you change the sheet reference if applicable
On Error Resume Next
For Each nm In Names 'Loop through all sheet names.
sh.Range("A" & Rows.Count).End(xlUp)(2)=nm.Name
sh.Range("B" & Rows.Count).End(xlUp)(2)="'" & nm.RefersTo
Next nm
On Error GoTo 0
End Sub
The above VBA coding is a simple procedure which will show all of the named ranges in a file. The error trap is there just in case there are no named ranges in the file.
