Excel VBA 複数シートのデータを集計シートにまとめる

Sub 集計シート作成()
Application.ScreenUpdating = False
　Dim i As Long
　Dim maxcol As Long
　Dim maxrow As Long
　For i = Sheets.Count To 1 Step -1
　　If Application.WorksheetFunction.CountA(Worksheets(i).Cells) = 0 Then
 Application.DisplayAlerts = False
 　　　　Worksheets(i).Delete
 Application.DisplayAlerts = True
　　End If
　Next i
 　Sheets.Add (after:=Sheets(Sheets.Count)).Name = "集計シート"
 　Sheets(1).Activate
 　Sheets(1).Range("A1").CurrentRegion.Select
 　Selection.Resize(Selection.Rows.Count - 1).Copy
 　Sheets("集計シート").Activate
　 Sheets("集計シート").Range("A1").PasteSpecial
　For i = 2 To Sheets.Count
 　Sheets(i).Activate
 　Sheets(i).Range("A1").CurrentRegion.Select
　 Selection.Offset(1).Resize(Selection.Rows.Count - 2).Copy
　 Sheets("集計シート").Activate
 　Sheets("集計シート").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
　Next i
　 maxcol = Cells(1, Columns.Count).End(xlToLeft).Column
　 maxrow = Cells(Rows.Count, 1).End(xlUp).Row
　With Cells(maxrow, 2).Offset(1)
　 .Resize(, maxcol - 1).Formula = "=SUM(" & Range("B2", Range("B" & maxrow)).Address(False, False) & ")"
　End With
 　Cells(maxrow, 1).Offset(1) = "総合計"
 　Columns.AutoFit
　 Range("A1").CurrentRegion.Select
 　Selection.Borders.LineStyle = True
 　Range(Cells(maxrow + 1, 1), Cells(maxrow + 1, maxcol)).Interior.Color = RGB(192, 192, 192)
Application.ScreenUpdating = True
End Sub

3行目【Dim i As Long】
繰り返し処理の中で使用する、シートのインデックス番号を格納するカウンター変数「i」を長整数型(Long)で宣言します。

4行目【Dim maxcol As Long】
追加してデータを集めた「集計シート」のデータ範囲の最終列番号を格納する変数「maxcol」を長整数型(Long)で宣言します。

5行目【Dim maxrow As Long】
追加してデータを集めた「集計シート」のデータ範囲の最終行番号を格納する変数「maxrow」を長整数型(Long)で宣言します。

6行目【For i = Sheets.Count To 1 Step -1】
データがないシートがあると後の処理でエラーになるため、空のシートを削除するための繰り返し処理の始まりです。
カウンター変数の「i」にシートのインデックス番号を代入しますが、ここでのポイントは、シート1から始まりではなく
Sheets.Countでシートの数を数えて、シートの最終つまり右からStep-1で左、つまりシート1方向に空セルが無いか確認して空セルがあった場合は、そのシートを削除します。右のシートから確認する理由は、左セルから見て空シートがあって削除した場合シートが左にずれるためその左にずれたシートがこの繰り返し処理から漏れてしまうためです。後ろからシートを削除した場合はシートがずれることがないためシート右端から確認します。

7行目【If Application.WorksheetFunction.CountA(Worksheets(i).Cells) = 0 Then】
ワークシート関数のCuntA(ｶｳﾝﾄｴｰ)関数を使って空のシートを条件分岐します。CuntA関数は、範囲に含まれる空白でないセルの個数を返す関数です。引数に範囲をしてします。ここでは、CountA(Worksheets(i).Cells)でワークシートのセルを確認し
データの個数が0だった時8行目からの処理をすることを定義しています。

8行目【Application.DisplayAlerts = False】
9行目で空のワークシートを削除するときに「本当に削除していいですか？」とアラートがでるのアラートをださない設定をします。詳しくはこちらをご覧ください。

9行目【Worksheets(i).Delete】
データのないシートを削除しています。

10行目【Application.DisplayAlerts = True】
確認アラートの停止を解除しています。

13行目【Sheets.Add (after:=Sheets(Sheets.Count)).Name = “集計シート”】
Addメソッドで「集計シート」という名前のシートを追加しています。シートの追加についてこちらをご覧ください。

14行目～18行目
【Sheets(1).Activate
Sheets(1).Range(“A1”).CurrentRegion.Select
Selection.Resize(Selection.Rows.Count – 1).Copy
Sheets(“集計シート”).Activate
Sheets(“集計シート”).Range(“A1”).PasteSpecial】
Sheets(1)でシートをインデックス番号で指定しています。(1)は先頭のシート　すなわちここでは、東京店のシートです。
東京店のシートのデータ範囲をCurrentRegion(ｶﾚﾝﾄﾘｼﾞｮﾝ) プロパティで選択し、最終の合計行はいらないのでResize(ﾘｻﾞｲｽﾞ)プロパティでResize(Selection.Rows.Count – 1)とし、最終行を選択状態からはずして、copy(ｺﾋﾟｰ)メソッドでコピーをして、追加した集計シートのA1セルにPasteSpecial(ﾍﾟｰｽﾄｽﾍﾟｼｬﾙ)メソッドで、貼付けています。

19行目～25行目
【For i = 2 To Sheets.Count
Sheets(i).Activate
Sheets(i).Range(“A1”).CurrentRegion.Select
Selection.Offset(1).Resize(Selection.Rows.Count – 2).Copy
Sheets(“集計シート”).Activate
Sheets(“集計シート”).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
Next i】
For文で各シートのデータをコピーして
集計シートのデータ最終行に貼り付けています。sheet(2)以降は1行目の項目行もいらないので、A1セルからCurrentRegionで選択した範囲をSelection.Offset(1)で1行下げて（選択範囲を1行下にずらしている状態）.Resize(Selection.Rows.Count – 2)で選択範囲の最終行から-2行することで合計行をコピーの選択範囲から除外しています。

26行目27行目
【maxcol = Cells(1, Columns.Count).End(xlToLeft).Column
maxrow = Cells(Rows.Count, 1).End(xlUp).Row】
集計シートのデータ範囲の最終行数と列数を変数に代入しています。

28行目【With Cells(maxrow, 2).Offset(1)】
集計シートのデータの最終行の下行に合計行を作成するため、Cells(maxrow, 2).Offset(1)で2列目のmaxrow最終ﾃﾞｰﾀ行のOffset(1)で1つ下の行を選択してWithステートメントで指定します。

29行目【 .Resize(, maxcol – 1).Formula = “=SUM(” & Range(“B2”, Range(“B” & maxrow)).Address(False, False) & “)”】
28行目で選択した2列目のセルに対してResize(, maxcol – 1)で、データ範囲の最終列から、2列目から指定しているので-1することで2列目からデータの最終列を選択状態にしています。そこに、Formula = “=SUM(” & Range(“B2”, Range(“B” & maxrow)).Address(False, False) & “)”でSUM関数の式をFormulaプロパティに代入して上記で選択状態のセルに数式を代入しています。

31行目【Cells(maxrow, 1).Offset(1) = “総合計”】
1列目のデータ最終行の下のセルに、総合計の文字列を代入しています。

32行目【Columns.AutoFit】
列幅をデータ幅に合わせています。

33行目34行目
【Range(“A1”).CurrentRegion.Select
Selection.Borders.LineStyle = True】
データ範囲に罫線をひいてます。

35行目【Range(Cells(maxrow + 1, 1), Cells(maxrow + 1, maxcol)).Interior.Color = RGB(192, 192, 192)】
最後の総合計行を薄いグレーで塗りつぶしてします。

36行目【Application.ScreenUpdating = True】
止めていた画面の更新を再開しています。