Range Name Scope

[Gee]

I named my ranges using the "create from selection Tab" . I was not under the
impression that thisd defaults the scope of the name to the full workbook. I
have done many ranges this way and have used these in formulae. I need to
change the scope to the worksheet , and I dont want to have to redo the whole
sheet because of this .

How do I quickly change the scope of the range names ? I have tried it
through the Name manager but the scope is Greyed out.!!!

Please HELP ME anyone !!!!

GEE

 

[Yvonne007]

1. Click [Review] tab.
2. If there is 'Unprotect Sheet' instead of 'Protect Sheet', Click
'Unprotect Sheet'
3. If 'Password' popup is displayed, enter the Password.
4. And check [Formulas]-[Name Manager].

Thanks.

 

[Dave Peterson]

If you have to localize (or globalize) and existing name, you'll want to use Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

In fact, if you're working with names, you'll want this!

 

[Gee]

:
Thanks Dave , I did find the Name manager by looking for my subject on
previous threads , but thanks for the response. 3 Further questions if you
dont mind:

1. In the Name Manager by Karel Pieterse, one could only change 1 name at a
time else it came up with an error "list has been changed". Do u know if this
is correct or am I using it wrong. Yesterday I had to change over 300 names
one by one. Bit tedious.
2. The button Formula Create Selection , where the Names are adjacent to the
cell and you just select whioch side , a very useful feature , but it always
makes the names Global. Do you know if you can change the default to local
for this feature.
3. And here I am taking a chance :

Do you know of any addins , tutorials etc on Pivot Charting Techniques --
The Microsoft E-Learning does not cover this ,

Thanks and Regards
GEE

 

[Dave Peterson]

#1. I've never seen that message -- but I haven't used the name manager to do
what you're doing enough.

#2. I don't see a way using the built in menus. But you could create your own
macro that does it. You could make it as complex as you want or as simple as
you want.

This assumes that the ranges are on the sheet that gets the names (First column
has the name, second has the address):

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim TestRng As Range
Dim NewName As String

Set myRng = Selection.Areas(1).Columns(1)
For Each myCell In myRng.Cells
Set TestRng = Nothing
On Error Resume Next
Set TestRng = ActiveSheet.Range(myCell.Offset(0, 1).Value)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Failed as range with: " & myCell.Address(0, 0)
Else
NewName = "'" & ActiveSheet.Name & "'!" & myCell.Value
On Error Resume Next
TestRng.Name = NewName
If Err.Number <> 0 Then
MsgBox "Failed as name with: " & myCell.Address(0, 0)
Err.Clear
End If
On Error GoTo 0
End If
Next myCell

End Sub

You could modify it so that you include the name. (I'd use a separate column to
make life easier.)

First column has the name, second has the address and third has the sheet name.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim TestRng As Range
Dim NewName As String
Dim TestWks As Worksheet

Set myRng = Selection.Areas(1).Columns(1)
For Each myCell In myRng.Cells
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(myCell.Offset(0, 2).Value)
On Error GoTo 0

If TestWks Is Nothing Then
MsgBox "Failed as worksheet: " & myCell.Address(0, 0)
Else
Set TestRng = Nothing
On Error Resume Next
Set TestRng = TestWks.Range(myCell.Offset(0, 1).Value)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Failed as range with: " & myCell.Address(0, 0)
Else
NewName = "'" & TestWks.Name & "'!" & myCell.Value
On Error Resume Next
TestRng.Name = NewName
If Err.Number <> 0 Then
MsgBox "Failed as name with: " & myCell.Address(0, 0)
Err.Clear
End If
On Error GoTo 0
End If
End If
Next myCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


#3. Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx