「それぞれのシートにある月次データを一括してまとめる方法」の例をとおして、複数のワークシートをまとめて集計するときのマクロの作り方をご紹介します。
今回はおもに「For Each ~ Next」と「If ~ Then」を使います。
実用的な内容に近づいていますのでプログラムの動きのイメージを持てるようにしておきましょう。
こちらでご紹介するおもな内容です。
- 「For Each ~ Next」の実用的な使い方
- 「If ~ Then」の実用的な使い方
- 最終行を取得する方法
シートの構成｜複数シートのまとめ
それでは、集計用シート「月集計」に各月のデータを反映させるマクロを作成します。
月次の売上データを一覧集計するために使います。
こちらが集計用のシートです。
こちらが各月のデータです。
1月、２月、３月のシートを各月の売上データとします。
事前準備
まずは変数設定が必要なところを考えましょう。
こちらの場合、月別シートと集計用シートの値が状況によって変動することが考えられます。
「月別シート」は月数を重ねるごとに追加されますし、その月の取引量によって最終行もまちまちです。
また「集計用シート」はその月の取引量によって最終行が変動しますね。
変数の設定
それでは変数の設定をしておきましょう。
集計用シート
「月別シート」の貼付ごとに最終行が変わるため設定をします。
Dim last_row_shukei As Long
まずはこの段階では、「変数の型」のみを設定します。
「月別シート」の貼付ごとに最終行の値が変化するため、のちほど繰返処理によって最終行の値をつど更新させる設定をします。
コードの先頭部分で最終行の値を固定してしまうと、繰返処理によって同じ場所に何度も「月別シート」の値が貼付されることになってしまうためです。
各ワークシート
月数が増えるごとにシート数が変わるため設定します。
Dim last_row_tsukibetsu As Long
Dim ws As Worksheetこちらも「変数の型」のみの設定をします。
先ほどとおなじ理由で、コードの先頭部分で最終行の値を固定してしまうと、繰返処理の貼付によって行が下に追加されていくのではなく、同じ場所に上書きが繰り返されてしまうためこの時点では最終行の設定はおこないません。
コードの構成｜「For Each ~ Next」と「If ~ Then」
まずはコード全体をご確認ください。
のちほどポイントごとに内容を解説します。
Sub ws_shukei()
Dim last_row_tsukibetsu As Long
Dim ws As Worksheet
Dim last_row_shukei As Long
For Each ws In Worksheets
If ws.Name <> "月集計" Then
'手順1.「シート：月別」からデータをコピーする
last_row_tsukibetsu = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.Select
Range(Cells(2, 1), Cells(last_row_tsukibetsu, 3)).Copy
'手順2.「シート：月集計」にペーストする
last_row_shukei = Worksheets("月集計").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("月集計").Select
Cells(last_row_shukei, 1).Select
ActiveSheet.Paste
End If
Next
MsgBox ("マクロ完了")
End Subこちらが実行結果です。
ワークシート単位で繰返処理｜6行目
For Each ws In Worksheets
19行目「Next」までのあいだでワークシートにたいして繰返処理をしています。
「ws」が変数部分で、「Worksheets」がコレクション名です。
ワークシートの名前で判断｜7行目
If ws.Name <> “月集計” Then
「もしワークシートの名前が「月集計」ではない場合」と条件分岐の設定をしています。
「月別シート」の最終行の取得｜9行目
last_row_tsukibetsu = ws.Cells(Rows.Count, 1).End(xlUp).Row
シートによって値が変わるため、繰返処理のなかで値を取得しています。
最下行から上に向かって進み、最初に値が入っていたセルを最終行とみなしています。
値のコピー｜10行目と11行目
ws.Select
Range(Cells(2, 1), Cells(last_row_tsukibetsu, 3)).Copy
まとめて1行で記述したいところですが、まとめるとエラーになります。
いったんワークシートを選択して、それからセルをコピーする手順で記述します。
「集計シート」の最終行の取得｜14行目
last_row_shukei = Worksheets(“月集計”).Cells(Rows.Count, 1).End(xlUp).Row + 1
「月別シート」とおなじ考え方です。
シートによって値が変わるため、繰返処理のなかで値を取得しています。
ただ、こちらは「最終行のひとつ下」を指定しています。
「最終行」のままだと1行分を上書きしてしまうため、行に「1」を追加することによって調整をしています。
値の貼付｜15行目～17行目
Worksheets(“月集計”).Select
Cells(last_row_shukei, 1).Select
ActiveSheet.Paste
こちらも1行でまとめたいところですが、エラーがでるため分けて記述しています。
「集計シート」を選択して、「最終行（+1）」を選択して貼付するといった流れです。
完了のメッセージ｜20行目
MsgBox (“マクロ完了”)
マクロの最後にメッセージボックスを設定しています。
画面だけでは「いつマクロが完了したか」判断ができないときに使いましょう。
まとめ
シンプルなマクロとは言えませんが、実用的な例をご紹介いたしました。
転用できる点もいくつかあると思いますので、今回ご紹介したコードの内容を書き換えて、自分なりのシート集計にぜひチャレンジしてみてください。