同じフォーマットのシートが複数あると集約が必要になる

事務作業でよくあるのが、同じフォーマットで複数のシートに入力する作業です。例えば、月ごとや日ごとの在庫管理シートや、売上管理の担当者シートなどです。

そしてそのようなブックを扱っていると後になって必要になるのが、複数シートの集約です。1年分にまとめたい、とか、全担当者分をまとめたい、とかですね。

このようなフォーマットは組織ごとに異なるため、集約方法にも当然のように違いがあるためそれら全てに対応することは出来ませんが、「全シートを1つにまとめたい」という目的は大体同じなので、ここではどのようなブックでも扱える「集約」に特化したコードを紹介します。


全シートの内容を新しいブックで1つにまとめる

以下のコードを実行すると、全シートの内容を新規ブックを作成して1つのシートに全て貼り付けます

関数が3つありますが、実行するのはMergeSheets()です。残りの2つはMergeSheets()から呼び出されるサブ関数です。

処理内容はコメントに大体書いていますが、考え方について補足します。

まず、まとめたいシートがあるブックを選択しておきます。そしてMergeSheets関数を実行すると、GetSheetData関数の中でコピー元の全シートをループして、各シートのセル入力範囲の内容をRangeオブジェクトとして取得します。取得はRangeオブジェクト配列(変数ar)に格納します。セル入力範囲の判定はUsedRangeを使っているため、シートごとにフォーマットが同じでも異なっていても、セル入力範囲は全てコピー対象になります。

その後、OutputData関数に全シートのデータ(配列ar)を渡して呼び出し、新規ブックを作成して、一番左のシートの上から順に全シートの内容が張り付けられます。

張り付ける順番は元のシートの左から順です。例えば、「1月」「2月」「3月」というシートが左から順にある場合は、新規ブックのシートには上から順に「1月」の内容、「2月」の内容、「3月」の内容が張り付けられます。

なお、コピーしたくないシートがある場合は、GetSheetData関数のsExcList変数にそのシート名を書いておけばコピーの対象にならないようにしています。そのようなコピーから除外したいシートが複数ある場合はコロン(:)でシート名を区切ってください。例えば、「1月」から「12月」までの12個のシートがある場合に、「4月」と「5月」と「12月」のシートをコピーしたくない場合は、「sExcList = “4月:5月:12月”」としてください。除外したいシートが1つだけの場合はコロンは不要です。

以下のコードでは元のシートの書式や値を全てコピーしていますが、値だけを張り付けたい場合はコメントアウトしている.Valueのコードを使えばそのようになります。



使い方

使い方ですが、集約したいシートのブックを表示させて、GetSheetData関数を実行するだけです。実行すると、新規ブックの一番左のシートに集約されます。

なお、新規ブックの保存は行わないため、それは手で保存してください。保存処理を入れたい場合は「VBAでブックに名前を付けて保存する(SaveAs)」をご参照ください。

以下は使い方の例です。シートが5つあり、2つのシートをコピーから除外するようにしています。除外したいシートは上で書いている通りですが変数sExcListに記述しておきます。ここでは「sExcList = “Sheet1:Sheet1 (3)”」としています。

元の5つのシートが以下です。それぞれ行数が異なり、また、罫線やテーブルの種類も異なります。4つ目のシートは列数を変えています。

GetSheetData関数実行後に新規ブックに以下のように集約されます。元のシートの書式がそのままで反映されます。2つのシートが含まれていないのは変数sExcListで除外しているためです。

自動で保存はしていませんので手で保存してください。