複数シートのデータを1枚のシートに集約する

どんな場面で使うか

支店ごと・担当者ごとにシートが分かれた報告書を、月末に1枚にまとめて集計する作業はよくあります。シートが数枚なら手作業でもできますが、毎月数十枚をコピー＆ペーストするのは時間がかかるうえ、貼り付け漏れや二重貼り付けの原因になります。

この記事のマクロは、ブック内の全シートのうち集計用シート以外のデータを、1枚のシートに縦に積み上げます。どのシートから来たデータかが分かるよう、先頭列にシート名を付けます。

前提とするシートの形

各シートの1行目が見出し、2行目からデータで、列の並びがすべてのシートで同じであることを前提にします。列の順番がシートによって違う場合は、先に形式をそろえてから実行してください。

• 1行目：見出し（全シート共通）
• 2行目以降：データ
• A列：必ず値が入る列（最終行の判定に使う）

コード

集計用シート「集約」があれば中身を消して作り直し、なければ新しく作ります。データの転記は配列でまとめて行うので、行数が多くても速く動きます。

Sub MergeSheets()
    Const OUT_NAME As String = "集約"
    Dim wb As Workbook, ws As Worksheet, outWs As Worksheet
    Dim lastRow As Long, lastCol As Long, outRow As Long
    Dim data As Variant

    Set wb = ThisWorkbook
    On Error Resume Next
    Set outWs = wb.Worksheets(OUT_NAME)
    On Error GoTo 0
    If outWs Is Nothing Then
        Set outWs = wb.Worksheets.Add(Before:=wb.Worksheets(1))
        outWs.Name = OUT_NAME
    Else
        outWs.Cells.Clear
    End If

    Application.ScreenUpdating = False
    outRow = 2
    For Each ws In wb.Worksheets
        If ws.Name <> OUT_NAME Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            If outRow = 2 Then                      ' 見出しは最初の1回だけ
                outWs.Range("A1").Value = "シート名"
                outWs.Range("B1").Resize(1, lastCol).Value = ws.Range("A1").Resize(1, lastCol).Value
            End If
            If lastRow >= 2 Then
                data = ws.Range("A2").Resize(lastRow - 1, lastCol).Value
                outWs.Cells(outRow, "A").Resize(lastRow - 1, 1).Value = ws.Name
                outWs.Cells(outRow, "B").Resize(lastRow - 1, lastCol).Value = data
                outRow = outRow + lastRow - 1
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
    MsgBox outRow - 2 & " 件を集約しました"
End Sub

コードのポイント

集約先のシートを毎回クリアしてから貼り付けるので、何度実行しても二重に貼り付けられることはありません。見出しは最初のシートから1回だけ転記し、2枚目以降はデータ部分だけを転記しています。

Resize を使って貼り付け先の大きさをデータと同じにし、Value どうしを代入しているため、コピー＆ペーストに比べて書式は引き継がれませんが、その分速く確実です。書式も必要な場合は、最後に集約シートの見出し行だけ書式を整えるのがおすすめです。

注意点

非表示のシートや、メモ用のシートがあると、それも集約の対象になります。対象外にしたいシートがある場合は、シート名で除外する条件を If 文に追加してください。また、別のブックのシートを集める場合は、Workbooks.Open で開いたブックを wb に指定すれば同じ考え方で処理できます（フォルダ内のファイルを一括で処理する方法は別の記事で解説しています）。

ほかの記事