Excel VBA 全てのシート名を一括で変更しまくれるマクロ 個人用マクロブックに組み込めるよ

Excel カスタマイズ

スポンサーリンク

はじめに

Excelで、

    • 「1日」~「31日」の名前のシートを作らなきゃいけない
    • 既に何十個ものシートを設置してるけど、それらのシート名を全部変えていかないといけない

といったように、多数のシート名を設定しないといけない場面は、きっと実務でも起こると思う。

普通はそういう場合、1個1個地道にシート名変更をやっていかないといけなくて、シート数が多いと大変だ。

シート名って、シート見出しをダブルクリックしないといけなくて手間が掛かるしね。
ショートカットキーもワンタッチのものはないし。

今回はそういうケースのために、Excelのブック中のシート名を、全て一括で変更できるマクロを紹介する。

マクロの動作イメージ

まずはマクロの動作イメージから示す。先に動画から。

たとえば下図のExcelファイルには9個のシートがあって、これらのシート名をB1~B9セルに示した通りに変更したいとする。

そのB1~B9セルの範囲を過不足なく選択してからこのマクロを起動すると、その通りに全てのシート名が変更されるっていうマクロだ。

ついでに、変更前・後のシート名がどう対応しているかっていうのを示す簡易的な一覧表も、別のExcelブックとして出力するようにしてある。

動作条件

このマクロには幾つか動作条件とか設定してあるので、それも示す。
まあ後述のソースコードを見て、読める人には分かるのだけどね。

「このシート名にしたい!」というシート名を書き並べるのは、どのシートのどのセル範囲でも良い。

先述の図の例では、「このシート名にしたい!」というシート名を「Sheet6」シートのB1~B9セルに書き並べていたけど、別にこれはどのシートでも良いのね。

適当なシートの、どこか適当な空いたセルに書いていけば良い。

「このシート名にしたい!」というシート名は、縦に連続して書かれていないといけない。

先述の例ではB1~B9セルに縦に書いてたけど、横のセル範囲については無視する。
また、Ctrlキーにより「B1,B3,B6」みたいな離れたセルを選択してたら駄目。

「このシート名にしたい!」セル範囲を選択しておくが、これはシート数より多くても少なくてもいけない。

シート数が全部で9個だったら、過不足なくちょうど9個だけ選ばないといけないわけ。

ブックが保護されている場合は、マクロは動作しない。

ブックが保護されている場合は、シート名が変更できないよう保護されているということになるから。
ブックの保護を解除するという手段もあるが、そういう処理は入れなかった。

選択範囲の中に空白セルがあった場合は、それに対応するシート名は変更しない。

左から3番目のシート名が「シート03」で、選択範囲の上から3番めが空白だったなら、左から3番目のシート名は「シート03」のままということね。
空白セルがあったら作動しないようにというのも考えたが、それはやめた。

ソースコード

それでは、今回のマクロのソースコードを示す。
このマクロは、Excelの個人用マクロブックに組み込んで使うと良いだろう。

個人用マクロブックとかについては、ここを展開。

オリジナルのマクロをリボンに追加する方法はこちら

個人用マクロブックについては、こちらこちらで述べています。

 

で、1つ注意。
今回のマクロでは、シート名の重複をチェックするために、連想配列というものを使っている。

連想配列についてここでは詳説はしないが、とにかくマクロで重複チェックをするのに便利なツールだ。
これを使うに当たっては

    Alt+F11でVBEを開く。
    「ツール」→「参照設定」とし「Microsoft Scripting Runtime」にチェックを入れる

という設定を、個人用マクロブックに対し先に済ませておこう。

これをしとかないと、連想配列が作動してくれない。

前置きが長くなったが、いよいよソースコード。
下記のソースコードの中で
changeSheetsNames
というやつがメインとなる実行プロセスなので、これをリボン等に組み込んで実行すれば良い

ソースコード中の

    fncDeleteStrings
    fncSheetNameModify

というやつで、シート名に使えない文字を削除するプロセスを組み込んでいる。
その他、シート名の付け方の規則だとかについて、別に解説ページを書いているので、そちらでどうぞ。

Excel VBA 不特定多数の不要文字をまとめてParamArrayで削除置換するマクロ→シート名に使えない文字を削除して文字列整形

2018年5月24日

もう既にだいぶ記事がグダグダ長くなったので、ソースコードの解説とかは書きません。
もしTwitterで当記事のこと書いてリクエストでもしてくれたら、そのときは書きますけどね。

スポンサーリンク