change range names on a particular sheet

[Ken]

I use the following code to change the names of all the rangenames in
a particular workbook:

Sub chng()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If Left(nm.Name, 3) = "FFG" Then
nm.Name = "DDG" & Right(nm.Name, Len(nm.Name) - 3)
End If
Next nm
End Sub

Now, because I copied a sheet with a whole lot of rangenames, I have a
sheet with local rangenames that are the same as the global rangenames
on the original sheet. I need to modify the code, so it only changes
rangenames that are local to a particular sheet. Is there a property
of Names that I can use to limit the name changing to rangenames local
to a particular worksheet?

Thanks

Ken

 

[Vergel Adriano]

A name that is local to a worksheet will have the worksheet name as part of
the name. You can search the name for "!" to determine if it's local

Sub chng()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If InStr(1, nm.Name, "!") > 0 Then
If Left(nm.Name, 3) = "FFG" Then
nm.Name = "DDG" & Right(nm.Name, Len(nm.Name) - 3)
End If
End If
Next nm
End Sub

or you can also check the Parent's name. If it's equal to the workbook name,
then it's not local.

Sub chng2()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If nm.Parent.Name <> ThisWorkbook.Name Then
If Left(nm.Name, 3) = "FFG" Then
nm.Name = "DDG" & Right(nm.Name, Len(nm.Name) - 3)
End If
End If
Next nm
End Sub

 

[Ken]

Vergel

Thanks to your timely help I got it to work. I am not exactly sure
what was going on though.

I used this code:

Sub chng()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If InStr(1, nm.Name, "!") > 0 Then
If Left(nm.Name, 6) = "DDG!CG" Then
nm.Name = "DDG" & Right(nm.Name, Len(nm.Name) - 6)
End If
End If
Next nm
End Sub

It crashed most of the time, and pointed me to the first End If
statement, with no clue what the problem was. When I set a break
point on the nm.Name= line and stepped through, it never crashed; but,
it never finished. It did the conversions I wanted, but, kept going
through the Names over and over until I stopped it. But, when I
stopped it, everything was changed. I used Jan Karel Pieterse' slick
Name Manager Add-in to convert the modified local range names to
global, and I am all set.

Thanks again

Ken