Worksheet level names

[AndreasHermle]

Dear Experts:

this macro, courtesy by Ron Rosenfeld ...

.... searches for the string 'Sales' and creates named ranges for the
respective current regions.

The created named ranges have got a workbook-level scope.

How would I have to re-write the macro to get worksheet level names
(local)? Say, the worksheet on which the ranges have to be named is
named 'Charting'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub Ranges_Create()
'Courtesy by Ron Rosenfeld, Google Groups
Dim c As Range
Dim firstAddress As String
Dim i As Long
i = 1
With Range("A1")
If .Value = "Sales" Then
.CurrentRegion.Name = "range" & i
firstAddress = .Address
i = i + 1
End If
End With


Set c = Cells.Find(What:="Sales", After:=Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)


Do While Not c Is Nothing And c.Address <> firstAddress
c.CurrentRegion.Name = "range" & i
i = i + 1
If firstAddress = "" Then firstAddress = c.Address
Set c = Cells.FindNext(c)
Loop

End Sub

 

[GS]

To define a name with local scope it must include the sheetname
(wrapped in single quotes followed by the exclamation character) in its
definition. You can programmatically assign local names as follows:

Range("$A$1").CurrentRegion.Name = "'" _
& ActiveSheet.Name & "'!" & sRngName

So if the sheet was named "Charting" and the value held in sRngName is
"Sales" then the above would read...

Range("$A$1").CurrentRegion.Name = "'Charting'!Sales"

...which is how you would define it in the Namebox or name field in the
Defined Names dialog if you manually selected the area and typed the
name.

 

[AndreasHermle]

To define a name with local scope it must include the sheetname
(wrapped in single quotes followed by the exclamation character) in its
definition. You can programmatically assign local names as follows:

  Range("$A$1").CurrentRegion.Name = "'" _
                & ActiveSheet.Name & "'!" & sRngName

So if the sheet was named "Charting" and the value held in sRngName is
"Sales" then the above would read...

  Range("$A$1").CurrentRegion.Name = "'Charting'!Sales"

..which is how you would define it in the Namebox or name field in the
Defined Names dialog if you manually selected the area and typed the
name.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry,

thank you very much for your professional help. Regards, Andreas

How come that I am not able anymore to rate the answers. I cannot find
any rating system. Strange, isn't it?

 

[GS]

AndreasHermle has brought this to us :

How come that I am not able anymore to rate the answers. I cannot find
any rating system. Strange, isn't it?

This forum is no longer supported by Microsoft, thus no way to rate
replies other than to acknowledge the helpfulness to the responders as
you just did here.

Thanks for the feedback; -always appreciated...

 

[The_Giant_Rat_of_Sumatra]

AndreasHermle has brought this to us :

This forum is no longer supported by Microsoft, thus no way to rate
replies other than to acknowledge the helpfulness to the responders as
you just did here.

Thanks for the feedback; -always appreciated...

Cool primer on scripted naming of ranges too! Thx!

 

[GS]

The_Giant_Rat_of_Sumatra brought next idea :

Cool primer on scripted naming of ranges too! Thx!

A utility you might find very useful is Jan Karl Peiterse's Name
Manager. You can download it here:

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp