【ExcelVBA】複数あるシート名を一括で変更する方法。変更前後のシート名が一覧で見れるので分かりやすい。

Excelはシートを増やすことで様々な情報を分けることができて便利です。
ですが、気が付いたら大量にシートができてしまった。
というのはよくあります。

この記事では、

そんな悩みを解決したいと思います。

やり方

シート名の変更は３ステップで行います。

1. 現在のシート名を取得する←マクロ
2. 変更後のシート名を入力←手動
3. シート名を変更する←マクロ

シート名を取得するコード

Sub シート名取得()
    Dim シート名リスト()
    'Worksheetsはすべてのシートが入っているのでそこから各シートを変数WS取り出します。
    For Each WS In Worksheets
        'Indexプロパティはシートの位置を表しているので、シート名リストの要素数をシートの位置と同じにします。
        ReDim Preserve シート名リスト(1 To WS.Index)
        'Nameプロパティはシート名を表しています。シート名リストの配列へ格納します。
        シート名リスト(WS.Index) = WS.Name
    Next
    '取得したシート名リストをA列へ貼り付ける
    Range("A1:A" & UBound(シート名リスト)) = WorksheetFunction.Transpose(シート名リスト)
End Sub

シート名を保存する配列を作ります。
この時点ではシート名の数がわからないので括弧の中の要素数は指定しません。

 

全てのシートから１つずつシートを取り出します。

Worksheetsはすべてのシートが入っている箱です。
Worksheetsの中身

そこからFor Each WS In Worksheetsで一つ目のシートを取り出します。
WSの中身

 

取得したシート名をシート名リストの配列に格納していきます。
その前に、シート名リストの要素数をシート番号に合わせます。
要素数を変えるにはReDim Preserveを使います。

要素数は配列名の後ろに括弧で書いて指定します。

この例だと要素数は２で、要素番号は1と2です。

今回は要素数はシートの数に合わせたいので取得するシート番号を要素数に指定します。

シート番号は先ほど取得したWSが持っているIndexで確認できます。

WS

WS.Indexと書けばシート番号を取得できます。

これでシート番号の確認方法がわかったので、ReDim Preserve シート名リスト(1 To WS.Index)で要素数を指定します。

シート名リストの中身

この時点では要素数は１つしかなく、中身は何も入っていません。

WS.Nameでシート名を取得します。

シート名リストの中身

処理がNextに到達するとWorksheetsから次のシートをWSに格納して同じ処理を繰り返します。
全てのシートの処理が終わるとNextの次に進みます。

全て処理した後のシート名リストの中身

最後にエクセルにシート名リストを張り付けていきます。

実行結果

３つあるシート名の[Sheet1][Sheet2][Sheet3]を
Range(“A1:A3”)に入力ができました。

手動で変更後のシート名を入力

A列は取得したシート名なのでそのままにします。
B列へ変更後のシート名を手動で入力します。

今回はローマ字からカタカナにしようと思います。

シート名を変更するコード

Sub シート名変更()
Dim シート名リストビフォー()
Dim シート名リストアフター()
最終行 = Cells(Rows.Count, 1).End(xlUp).Row

'WorksheetFunction.Transposeを使う事で1次元配列として取得
シート名リストビフォー = WorksheetFunction.Transpose(Range("A1:A" & 最終行))
シート名リストアフター = WorksheetFunction.Transpose(Range("B1:B" & 最終行))

    'シート分繰り返す
    For Each WS In Worksheets
        For i = 1 To UBound(シート名リストビフォー)
            'シート名がビフォー一致するかチェックして一致したらアフターに変更する
            If WS.Name = シート名リストビフォー(i) Then
                WS.Name = シート名リストアフター(i)
            End If
        Next
    Next
End Sub

シート名を保存する配列を作ります。
この時点ではシート名の数がわからないので括弧の中の要素数は指定しません。

 

変更前の情報が書かれている最終行を確認します。

このコードは最終行を取得するおまじないとして覚えておいてください。

 

A列から変更前のシート名、B列から変更後のシート名を取得します。

WorksheetFunction.Transposeは行と列を入れ替えるのでこのような形で保存されます。

シート名リストビフォー

シート名リストアフター

 

全てのシートから１つずつシートを取り出します。

 

取り出したシートのシート名がシート名リストビフォーと一致するか確認したいので、シート名リストビフォーの数だけ繰り返すようにします。

シート名リストビフォーは配列なのでForで繰り返します。

Uboundは括弧で書いた配列の要素数を表しています。
今回は[Sheet1][Sheet2][Sheet3]が入っているので３です。

ここで取り出したシートのシート名がシート名リストビフォーと一致するか確認します。

取り出したシート名(WS.Name)とシート名リストビフォーの1つ目(i)が同じか確認します。
ここはif文なので「WS.Name = シート名リストビフォー(i)」が同じならThenの後の処理を続けます。違う場合はEnd Ifまで飛びます。

変更前のシート名を見つけた場合は変更後のシート名に変える。

シート名リストビフォーとシート名リストアフターは表の同じ行に書いていたので、行数を表すiはそのまま使います。
変更後のシート名が入っているシート名リストアフターの内容を、実際のシート名であるWS.Nameに入れます。

 

実行結果

下に横並びになっている実際のシート名が[シート1][シート2][シート3]に変わりました。

VBAはどうやって覚える？

プログラムのコードが読めないと、参考にしているコードの理解や自分でプログラムを書くときに時間がかかってしまいます。
そんな時は基礎から学ぶと応用もできるようになり自分の作りたいプログラムがスムーズに作れるようになります。

無料でやるならネット上の学習サイトを参考にするといいです。
こちらはVBAの基礎から説明している無料のWEBサイトになります。

>>基礎から学べるWEBサイトを見てみる


過去にネットでの学習でつまずいた方には専門書をオススメします。
本なら基礎から学べる事はもちろん、読者視点で分かりやすい解説になっています。
一人で集中してコツコツ進めたい方は書籍を試してください。



本を読んでもわからない所が多すぎたり、誰かに質問したい場合はオンラインスクールを見てみましょう。
カウンセリングから目的に応じた学習プランと教材を提供してくれるので、プログラミングの敷居がとても低くなります。
誰かに相談できるのは心強いです。

>>オンラインスクールを無料体験で始める

まとめ

ExcelVBAで２つのコードを実行するだけで複数あるシート名の取得とシート名の変更を行う事ができるようになりました。
プログラムはコピペで使えるので、必要に応じて貼り付けて使ってください。

また、プログラムの構造はシートオブジェクトの取得を繰り返すよく使う処理なので、プログラム作成の型として使うこともできます。