working with named ranges & automated labelling

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:

Code:

---

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

---

Hi Erica, and welcome to VBAX!

Here's two options for you, depening on which route you want to go. Please note that there is no error handling in this, so if the sheet name already exists, it will bomb on you. We can fix that though as well.

Code:

---

Sub Ver1()
'Creates a new worksheet for each named range
'in the workbook
Dim xlName As Name, ws As Worksheet
For Each xlName In Parent.Names
    Set ws = Worksheets.Add
    ws.Name = xlName.Name
Next xlName
End Sub

Sub Ver2()
'Creates a new worksheet for each named range
'referring to the active worksheet only
Dim xlName As Name, ws As Worksheet, shtName As String
shtName = ActiveSheet.Name
For Each xlName In Parent.Names
    If Mid(xlName.RefersToR1C1, 2, Len(shtName)) = shtName Then
        Set ws = Worksheets.Add
        ws.Name = xlName.Name
    End If
Next xlName
End Sub

---

Let me know if you have any questions,

Code:

---

For Each xlName In Parent.Names
Set ws = Worksheets.Add
ws.Name = xlName.Name
Next xlName

---

can be simplified to

Code:

---

For Each xlName In Parent.Names
Worksheets.Add.Name = xlName.Name
Next xlName

---

saves a statement and a Set.

Hey cool!

Thanks, xld, and Welcome to VBAX! :thumb

Thanks all, problem solved! Very helpful indeed.....
:cloud9: