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