PDA

View Full Version : [SOLVED] working with named ranges & automated labelling



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

Ken Puls
04-29-2005, 01:47 PM
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
04-29-2005, 04:27 PM
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
04-29-2005, 04:38 PM
Hey cool!

Thanks, xld, and Welcome to VBAX! :thumb

ericac
05-02-2005, 01:01 AM
Thanks all, problem solved! Very helpful indeed.....
:cloud9: