エクセルVBAが思い通りのセルに名前を定義してくれません・・・

セルに名前を定義したマクロファイルが壊れてしまい、新しファイルに、表をセル範囲を選択してコピペしたものの、定義した名前は、消えてしまいました。
シートのコピーは、モジュールがありませんというエラーのためできずです。
壊れたファイルで定義されている30種類の名前を、新しいファイルの50シートぐらいに反映させたいコードの一例ですが、30種類の名前は、各シートの異なるセル番地で使用します。
下記のコードの間違いをどなたかご指摘いただけませんでしょうか?
各シート、各名前で異なるセル番地は、B列に入れたEND文字の入ったセルを起点として検索して反映したいと考えたコードが下記です。

Worksheet.Cells(Range("B:B").Find("END").Row - 4, 73).CurrentRegion.Name = Worksheet.Name & "XXX"

初心者のため、少ない情報かつわかりにくい質問ですみません。
なんとかこんな事をしたいのだろうと推測でも構いませんので、上記のコードが指定したセルに、範囲がREFにならずに、名前を定義してくれるように、アドバイスいただけませんでしょうか?
よろしくお願いいたします。

質問者からの補足コメント

• ENDの文字が入っているセルから上に４ヶめ右に73ヶめに定義したい場合のコードのつもりです。

    補足日時：2018/12/14 10:22

No.1 ベストアンサー 回答者： めぐみん_ 回答日時： 2018/12/14 08:52

Ｂ列を基点にするのはわかりますが、73列目からCurrentRegionってデータの集まりが離れているって事でしょうかね？

上記をそのままブックにある全てのシートに対して行なうとしたら、

Sub megu()
Dim ws As Worksheet

For Each ws In Worksheets
With ws
.Cells(.Range("B:B").Find("END").Row - 4, 73).CurrentRegion.Name = .Name & "XXX"
End With
Next
End Sub

このような感じでしょうか？
あるいはCurrentRegionが不具合ならResizeとかになりますけど、結局のところどの範囲に名前を付けたいのかが不明ですし。

この回答へのお礼

お察しいただきありがとうございます。
そのように修正して、試してみました。
XXXと定義した名前は、ちゃんと名前の管理に現れてくれました。
ところが、それぞれに1ヶのセル番地を指定しているはずなのに、
参照範囲が例えば、=ABF!$BC$302:$BE$307のように複数になってしまうのは、なぜでしょうか?
今セルの結合を解除しているところです。

お礼日時：2018/12/14 13:13

No.2 回答者： fujillin 回答日時： 2018/12/14 09:56

こんにちは

情報が少なすぎてよくわかりませんが・・・

＞範囲がREFにならずに、名前を定義してくれるように
それって、ご提示のコードが実行できていて、定義範囲がREFになってしまうということでしょうか？
実行できていれば、何らかの範囲が指定されるように思いますので、そのような結果になるとは思えないのですが・・？
実行できなくて（＝エラーで中断していて）、以前の定義（＝壊れた？ので、名前の参照がREFになっている）がそのまま残っているだけということはないのでしょうか？

ご提示のコードで気になる点
・WorkSheetって変数で使用することはないと思いますが（オブジェクト名なので）、ご提示のWorkSheet変数には、どこかで具体的なワークシートオブジェクトが代入されているのでしょうか？（そのままだと、未定義なのでエラーになる）
・B列の"END"が4行目以前の行に存在する場合やそもそも存在しない場合には、そのままのコードではエラーが発生すると思いますが、そのようなことは起きないことが保証されていますか？

ご質問文からひとまず想像できそうな回答例は、すでにNo1様がご提示済なので省略です。

この回答へのお礼

お察しいただきありがとうございます。
この質問用にwsをWorksheetとしてしまったので、実際にはコードは、実行できていました。
ただ指定したセルに定義されずでした。
＞名前の参照がREFになっている
それありえます。名前をきれいにしてから、No.1さんのアドバイスのFor Each ws In Worksheets With ws End Withを使ってやりなおしてみます。

お礼日時：2018/12/14 10:51