working with named ranges & automated labelling

[ericac]

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

[Ken Puls]

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.

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,

[Bob Phillips]

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

can be simplified to

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

saves a statement and a Set.

[Ken Puls]

Hey cool!

Thanks, xld, and Welcome to VBAX!

[ericac]

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