シートが多い場合はシート名の変更が面倒

シートが多いブック、ありますよね。で、そういうブックに限ってシート名がいまいちだったりします。

  1. 左から順に「1.xxx」「2.xxx」と連番が振ってある。途中にシートを追加した場合は?
  2. シート名と内容が合っていない。
  3. シート名がわかりにくい。

こういうシート名に困っている場合は、さっさと直してしまった方がいいです。

シートの一覧の取得

後述するマクロでは変更前と変更後のシート名を使うため、元のシート名がなんなのかを取得しておく必要があります。

元のシート名を取得する方法を「VBAで全シートにリンクする一覧を取得する」にて紹介していますので参照ください。

シートの種類はワークシート、グラフ、マクロ、ダイアログの4種類

シートにはワークシート、グラフ、Excel 4.0 マクロ、MS Excel 5.0 ダイアログ、の4種類があります。マクロやダイアログを使っていることはほとんどないと思いますが、一応それらのシートの名前も変換できるようにします。

そこでWorkSheetsオブジェクトではなくSheetsオブジェクトを使います。WorkSheetsオブジェクトはワークシートだけ、Sheetsオブジェクトは4種類全てのシートを扱います。

事前設定

以下のソースコードは連想配列のDictionaryクラスを利用しています。Dictionaryクラスを使うには事前に参照設定が必要です。

VBAのツールメニュー→「参照設定」を選択してダイアログを開き、「Microsoft Scripting Runtime」にチェックを付けるとDictionaryクラスを使うことが可能になります。

シート名を一括変換するマクロ

以下のマクロは下の図のように左に変換前のシート名、右に変換後のシート名を入力しておき、それらを選択した状態で実行します。

左上のセル(変換前シート名の1つ目、上の絵ではB4セル)はA1セルでもC2セルでもどこでも構いません。左右が対応していればOKです。

 

コード説明

このマクロの処理概要は以下の通りです。

  1. セル選択範囲の左を変更前、右を変更後とみなし、連想配列Dictionaryのキーに変更前シート名、値に変更後シート名を設定する。
  2. 全てのシートをループし、現在のシート名が連想配列Dictionaryのキーに一致した場合は、Dictionaryの値である変更後シート名をシート名として設定する。

シート名には31文字までしか設定できないことや、設定できない文字「: \ ? [ ] / *」があります。変更後シート名にそれらの制限に引っかかった場合はエラーとしてイミディエイトウィンドウに出力しています。

なお、非表示シートも非表示を維持したままでシート名を変更します。

利用方法

1. 新規のワークシートを追加して、変更前と変更後のシート名を書きます。そして、入力した部分を選択します(下の絵ではB4セルからC9セルの範囲を選択します)。なお、この変換前と変換後を書いたシートは変換対象にはなりません。

2. 上のマクロ(ChangeSheetName)を実行します。

3. 実行後、シート名が変わります。

変更前

変更後(Sheet1に変換前と変換後を書いている場合は「テスト用シート」とはならず「Sheet1」のままです)