エクセルシートの並び順が思い通り！シート名一覧で自由自在

エクセルVBAで大量のシートの並び替えを行います。並びを考慮しないシート順では仕事も捗りません。

シート一覧＋再配置マクロを使って問題解決します。

思い通りのシート並びになります。

こんにちは、じゅんぱ店長（@junpa33）です。

一つのエクセルBOOKで、その利用状況によって、みるみる使用シートの枚数が増えて40枚50枚になってしまった、ということは普通によくあることでしょう。

エクセルBOOKに入っているシートが多くなりすぎると、流石に使い勝手が悪くなってしまいます。

こんな状況の改善策の一つとして、使い勝手の良い様にシートを並び替えるという方法があります。

シートの知識

コンテンツ

シートの順番を使い勝手良く思い通りに並び替え

基本的に、シートの並び順を変更するためには、そのBOOKのシート名を全て抽出することが必要です。

さらに、その抽出したすべてのシート名をどこに格納するかによって、その後のVBAコード設計が大きく変わってきます。

そして、どこに格納するかという点では、大きくこの2つの方法があります。

それぞれのシート名を ” 一つづつ配列の要素にする ”　ことで並びを調整する方法
対象のエクセルBOOKで、新たに専用のシート（「シート名一覧」など）を作成しそのシートのセルにシート名を書き出す。＜専用のシートを新規エクセルBOOKに設置しても良いが、手間と効率面でオススメ外。＞

記事タイトルの「思い通りの順番」という趣旨に沿って考えると、新規シートを追加してそこにシート名を抽出するのが、実務実用的な面での王道になると思います。

ここからは、新たなシートを設置するタイプの並び替えの方法を説明して行きます。

コード作成の概要は以下の通りです。

VBAコード作成の手順

シート一覧表用の新規のシートの設置
全シート名の抽出と書き出し
シート名を意図する並び順に変更
並び替えたシート名をシート配置に反映させる

②のステップで、③ステップには行かずに、別の考え方として、書き出したリストを目次として、シート名から実際にそのページに飛べるようにしたり、内容の修正変更削除などの機能を付け加えることも出来ます。

目次へ戻る

シートの並び順を意図通りに変更するコード作成

VBAの作成としては、

新規シートでシート名をリスト化する。（シート一覧表作成）
シート名一覧で変更した並び順でシートを並び替える。（シート再配置）

という作業を行う２つのマクロの構成でコードを作成することになります。

作成するVBAコードは、出来るだけ簡潔になる様にしています。なので、シート名一覧表に上書き機能は付けていません。

一旦作成したシート名一覧表で、更にシート名を変更するとか、新しい内容の新規シートを追加する場合は、既に作成したシート名一覧表を手作業で削除してから改めて最初からこのマクロプロセスを実行する必要があります。

シート名一覧表の上書き機能が必要という場合は、こちらのマクロの記事をご覧ください。

2022年7月5日エクセル多用途もくじシートを自動作成するVBA

①シート一覧表用の新規のシートの設置

（「シート一覧作成」編）

最初の準備としてシート一覧用の新規シートを追加します。追加のVBAコードは以下のようになります。

VBA

Sub シート一覧作成()
    Dim NWSN As String
    Dim Sh As Worksheet
    Dim ShN As String
    Dim s As Long
    On Error Resume Next
        NWSN = "シート一覧" & Format(Date, "yymmdd") & _
            "_" & Format(Now, "hhnn")
        Worksheets.Add(before:=Worksheets(1)).Name = NWSN

（当ブログ記事「シート名一覧表作成マクロをブック登録して色んなエクセルで使う」より）

シート一覧作成プロシージャーの前半の部分になります。

追加した日付を付けたシート名で新規作成します。毎回の作成時でシート名が変わりますので、変数NWSHとします。

②全シート名の抽出と書き出し

（「シート一覧作成」編）

次に作成したシートに全シート名をリストアップしセルに書き込んでいきます。

VBA

        With Worksheets(NWSN).Cells(1, 1)
            .Value = "INDEX NO."
            .Offset(, 1) = "Sheet Name"
        End With
        s = 1
        For Each Sh In Worksheets
            ShN = Sh.Name
            With Worksheets(NWSN).Cells(s + 1, 1)
                .Value = s
                .Offset(, 1) = ShN
                Columns("A:B").AutoFit
            End With
            s = s + 1
        Next
    On Error GoTo 0
End Sub

（当ブログ記事「シート名一覧表作成マクロをブック登録して色んなエクセルで使う」より）

シート一覧作成プロシージャーの後半の部分になります。

シートのA列にはインデックス番号、B列にはシート名を書き込みます。

先頭行は項目行で、新規作成シート自身の名前もリスト化されます。つまり、シート目次兼用のリストということです。

①と②のVBAコードについて詳しくは、こちらをご覧ください。

2022年11月28日シート名一覧表作成マクロをブック登録して色んなエクセルで使う

③シート名を意図する並び順に変更

（「シート再配置」編）

リスト化したシート名を意図する並び順に変更します。

セルの上から順に並びますので、手作業でセルの配置換えを行います。

エクセルのソート機能を利用して並び替えることも出来ます。

ユーザー設定リストを外部リストから入力できる並び替えマクロは以下の記事で確認できます。

2022年10月29日エクセル並び替えマクロツールのトリセツとダウンロード

④並び替えたシート名をシート配置に反映させる

（「シート再配置」編）

リスト化した順にシートの並びを変更します。Moveメソッドを使ってシートを移動させます。

VBA

Sub シート再配置()
    Dim i As Long
        For i = 3 To Cells(Rows.Count, 2).End(xlUp).Row
            With Worksheets(1)
                Worksheets(.Cells(i, 2).Value).Move after:=Worksheets(i - 2)
                .Select
            End With
        Next i
End Su

Moveメソッドについては詳しくはこちらを参考にしてください。