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

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


--
Hope that helps.

Vergel Adriano


"Ken" wrote:

> 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

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

On May 1, 2:52 pm, Vergel Adriano
<VergelAdri...@discussions.microsoft.com> wrote:
> 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
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
>
> "Ken" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -