月報集計作業をVBAで簡素化する　その３

前回は「既存データ直下セル位置を取得する」方法を説明しました

今回は「追加したシート数に対応して集計作業を繰り返す」方法を説明します
 この手順を採用すると汎用性の高いコードに生まれ変わります

追加したシート数の数だけ集計作業を繰り返す　Do～Loop

追加したシート数が取得できれば
その回数だけ集計作業を繰り返すコードに変更すると
より汎用性が高まります
シート数の数は「Worksheets.Count」プロパティでLong型として取得できます
既存シートが「使用方法」「集計」の２つなので
「3」からWorksheets.Countまで集計作業を繰り返せばOKとなります
したがって構文の基本構造は
Dim i As long, j As Long
i=3
j=Worksheets.Count
Do Until i=j
集計作業
i=i+1
Loop
となります

 

新規シートの追加は「Addメソッド」を使います
構文は　式 . Add( Before, After, Count, Type )　です
引数の「Count」「Type」は利用しませんので無視してOKです
詳細はレファレンスを参照して下さい

 

追加した新規シートに追加データをペーストして
データ以外の行を削除してから「集計」シートに挿入します
挿入後にこの新規シートを削除します

基本パターンは上記パターンを利用しますが
最初のシートだけは別コードで行います
最初のシートだけはタイトル行を追加する作業が必要となるからです

基本パターンコードは

コードの解説をします
2行目で挿入セル位置を変数Targetに代入しています

 

3行目で挿入データをコピーしています
挿入データはセルA1から連続するデータなので
CurrentRegionで範囲を取得しています
またCurrentRegionを使っているので開始セルを指定しています

 

4行目で新規シートに貼り付けしています

5行目は貼り付けした新規シートで不要行の抽出をしています

6行目で確認メッセージを非表示にして
7行目で選択された行を全て削除しています
8行目で削除後の全データをコピーしてTargetに貼り付けしています
削除により全データの範囲が変化しているので
CurrentRegionではなくてUsedRangで範囲を取得しています

 

9行目で不要になった新規シートを削除しています

このままでもOKですが
コードを読みやすくするためにWith文を使って書き換えます

 

With文が入れ子状態になっていますので注意して下さい
1つ目のWith文は3行目から16行目までです
2つ目のWith文は6行目から14行目までです
3つ目のWith文は8行目から11行目までです

入れ子状態を明確にするためにインデントを有効に使って下さい

 

これで必要分だけ集計作業を自動で行ってくれるコードが作成されました
仕上げにもう少し追加します
「集計」シートは上下に移動することが多いので
先頭行を固定行として常にタイトル行が表示される状態にします

コードは

 

3行目でシートをフォーマットしています
4行目から9行目でタイトル行の設定をしています

 

完成コードは

 

この講義のPDFはMyWordPressにあります