Anyone know how to get the name of each named range in an excel worksheet and use those names to label new worksheets in the same workbook? I have been working on some code for automating multiple chart creation and would like to now label each new chart worksheet with the name of the named range which is the source data for the chart....
Please see code below:

Option Explicit

Public Sub multichrt()
    Dim i As Integer
    Dim x As Range
    Dim c As Chart
    Dim y As Names
    Dim s As Sheets
    Dim n As Object
    Dim xlName As Excel.Name
    Dim b As Name
    Dim l As Collection
    Dim sh As Variant
Set y = ActiveWorkbook.Names
'For Each xlName In y
    'docWord.Bookmarks(xlName.Name).Range.Text
        'Set b = Range(xlName.Value)
        'l.Add (b)
'Next xlName
For i = 1 To y.Count
        'If (i = Odd) Then
        'End
        Set x = Range(y(i))
        'Application.Goto Reference:="x"
        'Set c = ThisWorkbook.Charts.Add
        Set c = Charts.Add
        c.ChartType = xlLine
        'ActiveChart.SetSourceData Source:=Sheets("heightdata").Range("C2:C303"),
c.SetSourceData x, _
        PlotBy:=xlColumns
        'ActiveChart.Location Where:=xlLocationAsObject, Name:="heightdata"
        With ActiveChart
            .HasTitle = True
            '.ChartTitle.Characters.Text = "Building Height Analysis"
            .ChartTitle.Characters.Text = y(i)
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
            "Number of records"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Building Height (m)"
        End With
        'Set s = Application.Sheets
        'Set sh = s(GetLastSheet)
        'sh.Name = "Cstr (y(i))"
        Set n = y(i).Value
        'Set l = n.Name
        c.Name = CStr(n)
    Next i
End Sub