【VBA】Excelのシート名の一覧を取得し、変換する

【VBA】Excelのシート名を一括置換するというエントリを投稿したところ、『VBAマクロ書かなくても、ソフト使えば一括変換できるよ』的なコメントを頂いた。（ちょっとスパムっぽかったけどｗ）

以前のエントリは文字列置換を用いたシート名の変換だったが、教えてもらったものは「今のシート名」と「変更後のシート名」を一覧にして一括で置換するものだった。


便利そう！


でもシステム屋さんはお客様のPCを借りたり、サーバなど好き勝手インストールできない環境で仕事をしているので、インストールが必要なソフトは使えない。


でも、便利そう！！

これは、私のパーソナルマクロ集に加えたい！！！



ってことで、VBAマクロで再現してみた。

シート名の一覧を取得する

まずは現在のシート名の一覧を取得する。

Sub シート名取得()
    Dim ws As Worksheet
    Dim i As Long
    i = 0
    
    For Each ws In Worksheets
        Cells(Selection.Row + i, Selection.Column) = ws.Name
        i = i + 1
    Next
End Sub

Worksheetsをフェッチして、そのWorksheetのNameを取得して選択行から下に表示していく。
※選択行の下に文字があっても上書きされる。しかも、マクロで変更した箇所はUndoできないので要注意！

---

追記：2015/04/25 17:00
シート名取得の6行目と7行目の間に、
Cells(Selection.Row + i, Selection.Column).NumberFormatLocal = "@"

を追加することで、セルの書式を"文字列"にできる。
シート名がゼロ始まりや演算子始まりの場合は、NumberFormatLocalに@（アットマーク）を指定することで、頭落ちせずシート名が取得できる。

ys さんに教えていただきました。ありがとうございました。

---

追記：2015/07/14 20:00
グラフシートの名前が取得・置換できないとのコメントをいただいた。
今回のVBAでは「Worksheets」のみを対象にしているため、グラフシートの名前の取得、置換ができない。
グラフシートを表すオブジェクトは「Charts」のため、以下の内容を9行目と10行目の間に書き加えると実現できる。
グラフシート名の置換は次の章で...

Dim graph As Chart
For Each graph In Charts
    Cells(Selection.Row + i, Selection.Column) = graph.Name
    i = i + 1
Next

---

上記マクロを実行すると以下のようにシート名が取得できる。

シート名を一括で変換する

先ほどのマクロでシート名一覧を取得し、その右隣の列に「変更後のシート名」を書いておく。

そして、変換前と後のシート名を範囲選択してから、以下のマクロを実行すると……

Sub シート名変換()
    Dim ws As Worksheet
    Dim row, col As Long
    Dim wsNames As Collection
    
    Set wsNames = New Collection
    
    row = Selection.row
    col = Selection.Column
    
    Do While Not IsEmpty(Cells(row, col))
        ' KeyがStrig型でないと「型が一致しません」というエラーになる
        wsNames.Add Item:=Cells(row, col + 1).Value, _
                     Key:=CStr(Cells(row, col).Value)
        row = row + 1
    Loop
    
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.name = wsNames.Item(ws.name)
    Next
End Sub

こんな感じでシート名が一気に変わってくれる。

ちょいと面倒だが、1つ目のループで「wsNames」というコレクションに「変換前のシート名」と「返還後のシート名」を格納していく。
2つ目のループでKeyに変換前のシート名を渡して、Itemの変換後のシート名を取得し、シート名を書き換えている。

---

追記：2015/07/14 20:00
グラフシート名の置換は、以下の内容を21行目と22行目の間に書き加えることで実現できる。

Dim graph As Chart
For Each graph In ActiveWorkbook.Charts
    On Error Resume Next
    graph.name = wsNames.Item(graph.name)
Next

---

追記：2014/05/27
【VBA】Excelのシート名を一括置換する も合わせでどうぞ！


以上