Sorry.... change this:

With wsh
.Name = "Named Ranges"


To this:

With wsh
.Name = worksheetName


Last-minute changes never go smoothly!



On Aug 13, 3:58 pm, iliace wrote:
Here's a quick one for you. Enable macros, paste this somewhere
(personal macros workbook if you have one), activate the workbook for
which you want names, and hit Play. It will create a new worksheet in
that workbook called "Named Ranges" (and will delete any existing ones
by this name, so change that string if you have one), and list all the
ranged addresses and formulas in them.

Public Sub listNames()
' any worksheet by this name will be deleted and replaced
Const worksheetName As String = "Named Ranges"

Dim myName As Excel.Name
Dim wsh As Excel.Worksheet
Dim blnSetting As Boolean
Dim recordRow As Long

Application.ScreenUpdating = False

On Error Resume Next
Set wsh = ActiveWorkbook.Worksheets(worksheetName)
On Error GoTo 0

If Not wsh Is Nothing Then
blnSetting = Application.DisplayAlerts
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(worksheetName).Delete
Application.DisplayAlerts = blnSetting
End If

Set wsh = ActiveWorkbook.Worksheets.Add
recordRow = 2

With wsh
.Name = "Named Ranges"

If ActiveWorkbook.Names.Count Then
.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Refers To"
For Each myName In ActiveWorkbook.Names
.Cells(recordRow, 1).Value = myName.Name
.Cells(recordRow, 2).Value = "'" & myName.RefersTo
recordRow = recordRow + 1
Next myName
Else
.Cells(1, 1).Value = "No names defined in active
workbook."
End If
With .Range("A1:C1")
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
End With

Application.ScreenUpdating = True
End Sub

Hope this helps.

On Aug 13, 2:58 am, Melissa wrote:



Is there a way to visually show on the spreadsheet which cells have been
named and what the names are? Something like a coloured border around the
cells and the name displayed like a comment box? I don't actually need it in
this particular format but would just like to see where my named cells are so
I can edit them if I see errors. - Hide quoted text -

- Show quoted text -