ericac
04-29-2005, 04:50 AM
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
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