VBAでシート名を一括変換する｜ Excel作業をVBAで効率化

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

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

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

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

シートの一覧の取得

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

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

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

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

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

事前設定

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

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

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

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

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

Sub ChangeSheetName()
    On Error Resume Next
    
    Dim iRow                        '// ループ処理用の行
    Dim iCol                        '// 変更前シート名の列位置（＋１すると変更後シート名）
    Dim iRowStart                   '// 選択範囲の開始行
    Dim iRowEnd                     '// 選択範囲の終了行
    Dim map         As Dictionary   '// 変更前と変更後のシート名マップ
    Dim sht         As Object       '// シート
    Dim shtActive   As Object       '// アクティブシート
    Dim sSheetName  As String       '// シート名
    
    '// アクティブシートを取得
    Set shtActive = ActiveSheet
    
    '// 選択範囲の開始行と終了行と列を取得
    iRowStart = Selection.Row
    iRowEnd = Selection.Rows.Count + iRowStart - 1
    iCol = Selection.Column
    
    Set map = New Dictionary
    
    '// 選択範囲の変更前シート名をキー、変更後シート名を値としてマップに設定
    For iRow = iRowStart To iRowEnd
        Call map.Add(CStr(Cells(iRow, iCol).Value), CStr(Cells(iRow, iCol + 1).Value))
    Next
    
    '// 全シートループ
    For Each sht In Sheets
        sSheetName = sht.Name
        
        '// アクティブシートの場合は処理しない
        If sht.Name = shtActive.Name Then
            GoTo CONTINUE
        End If
        
        sht.Name = map.Item(sht.Name)
        
        If Err.Number <> 0 Then
            Debug.Print sSheetName & "は設定できません"
            Err.Clear
        End If
CONTINUE:
    Next
End Sub

Sub ChangeSheetName()

On Error Resume Next

Dim iRow '// ループ処理用の行

Dim iCol '// 変更前シート名の列位置（＋１すると変更後シート名）

Dim iRowStart '// 選択範囲の開始行

Dim iRowEnd '// 選択範囲の終了行

Dim map As Dictionary '// 変更前と変更後のシート名マップ

Dim sht As Object '// シート

Dim shtActive As Object '// アクティブシート

Dim sSheetName As String '// シート名

'// アクティブシートを取得

Set shtActive = ActiveSheet

'// 選択範囲の開始行と終了行と列を取得

iRowStart = Selection.Row

iRowEnd = Selection.Rows.Count + iRowStart - 1

iCol = Selection.Column

Set map = New Dictionary

'// 選択範囲の変更前シート名をキー、変更後シート名を値としてマップに設定

For iRow = iRowStart To iRowEnd

Call map.Add(CStr(Cells(iRow, iCol).Value), CStr(Cells(iRow, iCol + 1).Value))

'// 全シートループ

For Each sht In Sheets

sSheetName = sht.Name

'// アクティブシートの場合は処理しない

If sht.Name = shtActive.Name Then

GoTo CONTINUE

End If

sht.Name = map.Item(sht.Name)

If Err.Number <> 0 Then

Debug.Print sSheetName & "は設定できません"

Err.Clear

End If

CONTINUE:

End Sub

コード説明

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

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

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

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

利用方法

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

2. 上のマクロ（ChangeSheetName）を実行します。

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

変更前

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