エクセルで名前の定義の範囲をシートからブックに

質問

エクセルでシートの一定の参照範囲に名前の定義を行いました。
この時、範囲（参照範囲でなく）をシートでしたものを、あとからブックに変更はできないのでしょうか？
「名前の編集」を開くと、範囲はグレーになっており、ブックに変えることができないのです。
ご教示ください。

回答No.4

kkkkkm · ベストアンサー

既に同じ名前のブックの定義があった場合には新規作成されないと思っていたのですが上書きされてました。m(__)m
先に調べてAddした方がいいみたいです。あと、記載していませんでしたが標準モジュールに入れてください。

Sub Test()
Dim mName As String
Dim mRange As Range, c As Name
Dim i As Long, j As Long
Dim errflg As Boolean

For i = 1 To ActiveSheet.Names.Count
errflg = False
mName = Split(ActiveSheet.Names.Item(i).Name, "!")(1)
Set mRange = Range(ActiveSheet.Names.Item(i).Name)
For j = 1 To ActiveWorkbook.Names.Count
If ActiveWorkbook.Names.Item(j).Name = mName Then
ActiveSheet.Names.Item(i).Name = mName & "_Err"
errflg = True
Exit For
End If
Next
If errflg = False Then
ActiveSheet.Names.Item(i).Name = mName & "_Old"
ActiveWorkbook.Names.Add Name:=mName, RefersToLocal:=mRange
End If
Next
End Sub

お礼

何度もありがとうございました。
助かりました。

回答No.3

kkkkkm

> 要は、変更はできないのでシートの定義の名前を削除し、ブックの定義の同名の名前を付けるということですね？

はい、そうです。ただ問題は、既に同じ名前のブックの定義があった場合、新規作成より前に削除するので定義がなくなってしまいます。場合によっては削除より名前の変更が良かったかと思えます。

Sub Test()
Dim mName As String
Dim mRange As Range
Dim i As Long

For i = 1 To ActiveSheet.Names.Count
mName = Split(ActiveSheet.Names.Item(i).Name, "!")(1)
Set mRange = Range(ActiveSheet.Names.Item(i).Name)
ActiveSheet.Names.Item(i).Name = mName & "Old"
ActiveWorkbook.Names.Add Name:=mName, RefersToLocal:=mRange
Next
End Sub

お礼

ありがとうございます。

回答No.2

kkkkkm

No1です。

定義の数はCountできましたm(__)m
For i = 1 To ActiveSheet.Names.Count

お礼

ありがとうございます。

回答No.1

kkkkkm

VBAですが
シートの定義が2個の場合だと
(毎回削除するので.Item(1)は.Item(1).のままにしてください)
(データを取得して先に削除しないと同じ名前なので新規でできないみたいでした)
Sub Test()
Dim mName As String
Dim mRange As Range
Dim i As Long

For i = 1 To 2
mName = Split(ActiveSheet.Names.Item(1).Name, "!")(1)
Set mRange = Range(ActiveSheet.Names.Item(1).Name)
ActiveSheet.Names.Item(1).Delete
ActiveWorkbook.Names.Add Name:=mName, RefersToLocal:=mRange
Next
End Sub

お礼

ありがとうございます。
要は、変更はできないのでシートの定義の名前を削除し、ブックの定義の同名の名前を付けるということですね？