選択済セル範囲にシート名と同じ名前を付けるには?

Question 56.5 Previous Next
500連発第2弾マクロ番号120についてご質問します。
詳細は こちら テキストファイルの編集が終わり、名前を付けてExcel形式にて保存する直前に、有効データ範囲に WorkSheet名と同じ名前を付けるには、どの様にすればよろしいのでしょうか。
WorkSheet名を変数に代入するところまでは、思いつきます。ところが、範囲名を変数指定してもうまく行かないので(色々と記述を変えて試してみたのですが)、万策尽きてメールを差し上げる次第です。ご教授の程よろしくお願い申し上げます。
Answer   Copyright (C) 2002.11.26 永井善王
500連発第2弾をご愛読、ありがとうございます。
120番は「セル範囲に名前を付ける」と題する、ワークシートのセル範囲を指定して新しい名前を付けるマクロです。
Worksheet
Sub セル範囲に名前を付ける()
    Worksheets("Title").Activate
        ActiveWorkbook.Names.Add Name:="範囲名A", _
        RefersToR1C1:="=Title!R7C4:R10C6"
End Sub
右図は、あなたのご希望を具現化してみました。事前に選択されているセル範囲(例)A1:C4に、ワークシート名「テキストA」を使って名前をつけた結果、左上方の名前ボックスに「テキストA」と表示されています。

お悩みの点は 「範囲名を変数指定してもうまく行かない」とのことですので、上記のマクロの中の
RefersToR1C1:="=Title!R7C4:R10C6" を変数で指定する場合の書き方というよりも、その変数への値のセット方法かと思います。
そうだとすれば色々なコードが考えられますが、比較的わかりやすいサンプルを Macroのページに 「
選択済セル範囲にシート名と同じ名前を付ける」 として掲載しておきました。そちらでご覧ください。
実行時エラー
[注意事項]

ワークシートの名前に - (ハイフン)、 (スペース)等の Visual Basicの名前付け規則で決められている使用禁止文字を含む場合は、セル範囲の名前としては不適当であるため、実行時エラーになる場合があります。

Excel VBA Macro