はじめに：なぜ複数シートのデータ集約が重要なのか？
Excelを日常的に利用されている方なら、一度は「複数のシートに散らばったデータを、一つのシートにまとめたい」と思ったことがあるのではないでしょうか。例えば、月ごとの売上データ、部署ごとの顧客リスト、店舗ごとの在庫情報など、業務上、データを集約する必要性は非常に高いです。
手作業でコピー＆ペーストを繰り返すのは、時間もかかりますし、何よりミスの温床となります。データ量が多くなればなるほど、その負担は増大し、生産性を著しく低下させてしまいます。
そこで本記事では、Excel VBA（Visual Basic for Applications）を活用して、この煩雑な複数シートからのデータ集約作業を、驚くほど効率的かつ正確に行う方法を、ベテランExcel VBA講師の視点から徹底解説します。VBAの基本的な知識があれば理解できる内容ですので、ぜひ最後までお付き合いください。
VBAで複数シートのデータを1つのシートにコピーする基本的な考え方
VBAで複数シートのデータを集約する基本的な流れは、以下のようになります。
1. **集約先のシートを準備する**: データを集約するための「親」となるシートを用意します。
2. **集約元のシートを特定する**: どのシートからデータをコピーするかを明確にします。
3. **各シートのデータをコピーする**: 特定した集約元のシートから、必要な範囲のデータをコピーします。
4. **集約先のシートに貼り付ける**: コピーしたデータを、集約先のシートの適切な位置に貼り付けます。
5. **これを全ての集約元シートに対して繰り返す**: 全てのシートからデータを集約し終えるまで、3と4の工程を繰り返します。
この一連の作業を自動化するのがVBAの醍醐味です。
実践：VBAコードで実現するデータ集約テクニック
それでは、具体的なVBAコードを用いて、複数シートのデータを1つのシートにコピーする方法を見ていきましょう。
シナリオ設定
ここでは、「Sheet1」「Sheet2」「Sheet3」という3つのシートに、それぞれA列に商品名、B列に価格が入力されていると仮定します。これらのデータを、「Summary」という名前の新しいシートに、ヘッダー行を除いてすべて集約することを目標とします。
コード例1：特定のシート名を指定してコピーする方法
まず、集約したいシート名が固定されている場合のコードです。
Sub CopySpecificSheets()
Dim wsSummary As Worksheet ‘ 集約先のシートオブジェクト
Dim wsSource As Worksheet ‘ データ元のシートオブジェクト
Dim lastRowSummary As Long ‘ 集約先シートの最終行
Dim copyRange As Range ‘ コピーする範囲
‘ 集約先のシートを “Summary” という名前で設定
On Error Resume Next ‘ Summaryシートが存在しない場合のエラーを無視
Set wsSummary = ThisWorkbook.Sheets(“Summary”)
On Error GoTo 0 ‘ エラーハンドリングを元に戻す
‘ Summaryシートが存在しない場合は新規作成
If wsSummary Is Nothing Then
Set wsSummary = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsSummary.Name = “Summary”
End If
‘ Summaryシートをクリア（前回実行時のデータを削除するため）
wsSummary.Cells.ClearContents
‘ 集約元のシート名を配列で定義
Dim sheetNames() As Variant
sheetNames = Array(“Sheet1”, “Sheet2”, “Sheet3”) ‘ ここに集約したいシート名を追加・変更
‘ 集約元の各シートをループ処理
Dim sheetName As Variant
For Each sheetName In sheetNames
‘ シートが存在するか確認
On Error Resume Next
Set wsSource = ThisWorkbook.Sheets(sheetName)
On Error GoTo 0
If Not wsSource Is Nothing Then
‘ 集約先シートの最終行を取得（データがある場合）
lastRowSummary = wsSummary.Cells(Rows.Count, “A”).End(xlUp).Row
‘ データ元のシートの最終行を取得
Dim lastRowSource As Long
lastRowSource = wsSource.Cells(Rows.Count, “A”).End(xlUp).Row
‘ コピーする範囲を設定 (ヘッダー行を除く、A2から最終行まで)
‘ データが1行もない場合は、Range(“A2”) が存在しないためエラーになる可能性があるので、条件分岐を入れる
If lastRowSource >= 2 Then ‘ ヘッダー行以外にデータがある場合
Set copyRange = wsSource.Range(“A2:B” & lastRowSource)
‘ 集約先シートに貼り付け
‘ 最初の貼り付け（Summaryシートが空の場合）はA1から、それ以降は最終行の次から
If lastRowSummary = 1 And wsSummary.Cells(1, 1).Value = “” Then ‘ Summaryシートが完全に空の場合
copyRange.Copy wsSummary.Range(“A1”)
Else
copyRange.Copy wsSummary.Range(“A” & lastRowSummary + 1)
End If
End If
Else
MsgBox sheetName & ” という名前のシートは見つかりませんでした。”, vbExclamation
End If
‘ 次のシートのために wsSource をクリア
Set wsSource = Nothing
Next sheetName
MsgBox “複数シートからのデータ集約が完了しました。”, vbInformation
End Sub
**コードの解説:**
* `Dim wsSummary As Worksheet`, `Dim wsSource As Worksheet`: それぞれ集約先シートとデータ元シートを格納するための変数です。
* `Dim lastRowSummary As Long`, `Dim lastRowSource As Long`: 各シートの最終行を格納するための変数です。`Cells(Rows.Count, “A”).End(xlUp).Row` は、A列の最終行を効率的に取得する定番の方法です。
* `On Error Resume Next` / `On Error GoTo 0`: Summaryシートが存在しない場合にエラーで止まらず、処理を続行するためのエラーハンドリングです。Summaryシートがなければ新しく作成します。
* `wsSummary.Cells.ClearContents`: 集約先シートをクリアし、毎回きれいな状態から集約できるようにします。
* `sheetNames = Array(“Sheet1”, “Sheet2”, “Sheet3”)`: 集約したいシート名を配列で定義します。この部分を変更するだけで、対象シートを自由に変更できます。
* `For Each sheetName In sheetNames … Next sheetName`: 配列に格納されたシート名を一つずつ取り出し、ループ処理を行います。
* `If Not wsSource Is Nothing Then … End If`: 指定されたシート名が存在するかどうかを確認します。
* `If lastRowSource >= 2 Then … End If`: データ元シートにヘッダー行以外にデータが存在するかを確認します。データがない場合にコピーしようとするとエラーになるため、このチェックは重要です。
* `copyRange.Copy wsSummary.Range(“A1”)` または `copyRange.Copy wsSummary.Range(“A” & lastRowSummary + 1)`: データ元シートの指定範囲 (`copyRange`) をコピーし、集約先シート (`wsSummary`) の適切な位置に貼り付けます。初回はA1セルから、2回目以降は最終行の次の行から貼り付けます。
コード例2：シート名を指定せず、全てのシート（除外シートあり）を対象にする方法
場合によっては、特定のシートを除いた「全てのシート」を対象にしたいことがあります。そのような場合のコードです。
Sub CopyAllSheetsExceptSummary()
Dim wsSummary As Worksheet ‘ 集約先のシートオブジェクト
Dim wsSource As Worksheet ‘ データ元のシートオブジェクト
Dim lastRowSummary As Long ‘ 集約先シートの最終行
Dim copyRange As Range ‘ コピーする範囲
Dim excludeSheetName As String ‘ 除外するシート名
‘ 集約先のシートを “Summary” という名前で設定
On Error Resume Next
Set wsSummary = ThisWorkbook.Sheets(“Summary”)
On Error GoTo 0
If wsSummary Is Nothing Then
Set wsSummary = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsSummary.Name = “Summary”
End If
‘ Summaryシートをクリア
wsSummary.Cells.ClearContents
‘ 除外するシート名を設定
excludeSheetName = “Summary” ‘ 集約先シート自体は除外する
‘ 全てのシートをループ処理
For Each wsSource In ThisWorkbook.Worksheets
‘ 集約先シートまたは除外シートの場合はスキップ
If wsSource.Name <> excludeSheetName Then
‘ 集約先シートの最終行を取得
lastRowSummary = wsSummary.Cells(Rows.Count, “A”).End(xlUp).Row
‘ データ元のシートの最終行を取得
Dim lastRowSource As Long
lastRowSource = wsSource.Cells(Rows.Count, “A”).End(xlUp).Row
‘ コピーする範囲を設定 (ヘッダー行を除く、A2から最終行まで)
If lastRowSource >= 2 Then ‘ ヘッダー行以外にデータがある場合
Set copyRange = wsSource.Range(“A2:B” & lastRowSource)
‘ 集約先シートに貼り付け
If lastRowSummary = 1 And wsSummary.Cells(1, 1).Value = “” Then ‘ Summaryシートが完全に空の場合
copyRange.Copy wsSummary.Range(“A1”)
Else
copyRange.Copy wsSummary.Range(“A” & lastRowSummary + 1)
End If
End If
End If
Next wsSource
MsgBox “全てのシート（指定除外シートを除く）からのデータ集約が完了しました。”, vbInformation
End Sub
**コードの解説:**
* `For Each wsSource In ThisWorkbook.Worksheets`: `ThisWorkbook.Worksheets` コレクションを使うことで、ブック内の全てのシートを順番に処理できます。
* `If wsSource.Name <> excludeSheetName Then`: 現在処理しているシートの名前が、除外したいシート名（ここでは「Summary」）と異なる場合のみ、処理を実行します。これにより、集約先シート自身をコピー対象から除外できます。
* このコードは、集約したいデータが全てのシート（集約先シート以外）に均一な形式で存在する場合に特に有効です。
さらに高度なテクニック：条件付きコピーとエラーハンドリングの強化
ここまでのコードは基本的なデータ集約に十分ですが、実務ではさらに細かい条件や、予期せぬエラーへの対応が必要になる場合があります。
条件付きコピー
例えば、「特定の列に値が入っている行だけをコピーしたい」といったケースです。
Sub CopyWithCondition()
Dim wsSummary As Worksheet
Dim wsSource As Worksheet
Dim lastRowSummary As Long
Dim lastRowSource As Long
Dim copyRange As Range
Dim conditionColumn As String ‘ 条件判定の列
Set wsSummary = ThisWorkbook.Sheets(“Summary”) ‘ Summaryシートは存在するものとする
wsSummary.Cells.ClearContents
conditionColumn = “A” ‘ 例: A列に値がある行をコピー
For Each wsSource In ThisWorkbook.Worksheets
If wsSource.Name <> “Summary” Then
lastRowSummary = wsSummary.Cells(Rows.Count, “A”).End(xlUp).Row
lastRowSource = wsSource.Cells(Rows.Count, “A”).End(xlUp).Row
If lastRowSource >= 2 Then
‘ 条件に合う行のみを抽出する（ここではA列に値がある行）
Dim filteredRange As Range
Dim tempRange As Range
Dim r As Long
‘ まずはA2からB列の最終行までを一時的に取得
Set tempRange = wsSource.Range(“A2:B” & lastRowSource)
‘ A列に値がある行を filteredRange に追加していく
For r = 2 To lastRowSource
If wsSource.Cells(r, conditionColumn).Value <> “” Then
If filteredRange Is Nothing Then
Set filteredRange = wsSource.Cells(r, “A:B”) ‘ A列とB列をコピー対象とする
Else
Set filteredRange = Union(filteredRange, wsSource.Cells(r, “A:B”))
End If
End If
Next r
‘ 条件に合う行があった場合のみコピー
If Not filteredRange Is Nothing Then
If lastRowSummary = 1 And wsSummary.Cells(1, 1).Value = “” Then
filteredRange.Copy wsSummary.Range(“A1”)
Else
filteredRange.Copy wsSummary.Range(“A” & lastRowSummary + 1)
End If
End If
End If
End If
Next wsSource
MsgBox “条件付きデータ集約が完了しました。”, vbInformation
End Sub
**ポイント:**
* `conditionColumn = “A”` で条件判定する列を指定します。
* `For r = 2 To lastRowSource … If wsSource.Cells(r, conditionColumn).Value <> “” Then … Union(…)` の部分で、条件に合致する行を `Union` メソッドを使って結合し、まとめてコピーしています。
* この方法は、データ量が多いと処理に時間がかかる場合があります。より高速化したい場合は、`AutoFilter` メソッドなどを利用することも検討できます。
エラーハンドリングの強化
予期せぬエラー（例えば、特定のシートに想定外のデータ形式があった場合など）が発生した場合に、処理を中断するのではなく、エラー内容を記録して続行する、あるいはユーザーに通知するといった対応も可能です。
Sub CopyWithErrorLogging()
Dim wsSummary As Worksheet
Dim wsSource As Worksheet
Dim lastRowSummary As Long
Dim lastRowSource As Long
Dim copyRange As Range
Dim errorLog As String ‘ エラーログを格納する変数
On Error GoTo ErrorHandler ‘ エラー発生時の処理を定義
Set wsSummary = ThisWorkbook.Sheets(“Summary”)
wsSummary.Cells.ClearContents
For Each wsSource In ThisWorkbook.Worksheets
If wsSource.Name <> “Summary” Then
lastRowSummary = wsSummary.Cells(Rows.Count, “A”).End(xlUp).Row
lastRowSource = wsSource.Cells(Rows.Count, “A”).End(xlUp).Row
If lastRowSource >= 2 Then
Set copyRange = wsSource.Range(“A2:B” & lastRowSource)
If lastRowSummary = 1 And wsSummary.Cells(1, 1).Value = “” Then
copyRange.Copy wsSummary.Range(“A1”)
Else
copyRange.Copy wsSummary.Range(“A” & lastRowSummary + 1)
End If
End If
End If
Next wsSource
MsgBox “データ集約が完了しました。”, vbInformation
Exit Sub ‘ 正常終了時はここで終了
ErrorHandler: ‘ エラーハンドラ
errorLog = errorLog & “エラー発生：” & wsSource.Name & ” シートの処理中にエラーが発生しました。エラー番号: ” & Err.Number & “, 説明: ” & Err.Description & vbCrLf
MsgBox “処理中にエラーが発生しました。詳細についてはログを確認してください。”, vbExclamation
‘ エラーが発生しても、次のシートの処理を続行したい場合は Resume Next を使う
‘ Resume Next ‘ 例：エラーが発生した行の次の行から処理を再開する
‘ エラーログを新しいシートに書き出す（オプション）
Dim wsErrorLog As Worksheet
On Error Resume Next
Set wsErrorLog = ThisWorkbook.Sheets(“ErrorLog”)
On Error GoTo 0
If wsErrorLog Is Nothing Then
Set wsErrorLog = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsErrorLog.Name = “ErrorLog”
End If
wsErrorLog.Cells.ClearContents
wsErrorLog.Cells(1, 1).Value = “エラーログ”
wsErrorLog.Cells(2, 1).Value = errorLog
wsErrorLog.Columns(“A”).AutoFit
End Sub
**ポイント:**
* `On Error GoTo ErrorHandler`: コードの実行中にエラーが発生した場合、`ErrorHandler:` ラベルに処理がジャンプします。
* `Err.Number` と `Err.Description` でエラー番号とエラーメッセージを取得できます。
* `Resume Next` を使うことで、エラーが発生した箇所をスキップして処理を続行できます。
* エラーが発生した場合に、その詳細を別のシート（`ErrorLog`）に記録するようにしています。これにより、後で原因を追跡しやすくなります。
実務で役立つアドバイス
* **集約先シートのヘッダー行**: 集約先シートの1行目に、各シートのヘッダー行と同じ内容（例：「商品名」「価格」）を事前に設定しておくと、データ集約後の見栄えが良くなります。VBAコードでヘッダー行をコピーしないように調整するか、事前に手動で設定しておきましょう。
* **データ形式の統一**: 集約する各シートのデータ形式（列の順番、データ型など）が統一されていることが理想です。もし異なっている場合は、VBAコードで補正するか、事前にデータを整形する必要があります。
* **処理速度の向上**: データ量が多い場合、`ScreenUpdating` や `Calculation` の設定をOFFにすることで、処理速度を大幅に向上させることができます。
Application.ScreenUpdating = False ‘ 画面更新を停止
Application.Calculation = xlCalculationManual ‘ 計算を手動にする
‘ — VBAコード本体 —
Application.ScreenUpdating = True ‘ 画面更新を再開
Application.Calculation = xlCalculationAutomatic ‘ 計算を自動に戻す
* **ブックの保存**: 大量のデータを処理する前や、重要な処理を実行する前には、必ずブックを保存しておくことを習慣づけましょう。
* **コメントの活用**: VBAコードには、処理内容を分かりやすくするためのコメントを積極的に記述しましょう。後で見返したときに、コードの意図を理解しやすくなります。
* **デバッグ機能の活用**: VBAエディタには、コードの実行を一行ずつ確認できる「ステップ実行」や、変数の値を確認できる「ウォッチ」などのデバッグ機能があります。これらを活用することで、エラーの原因究明やコードの動作確認が効率的に行えます。
まとめ：VBAでデータ管理をスマートに
Excel VBAを使えば、複数シートに散らばったデータを、手作業では考えられないほどの短時間で、かつ正確に1つのシートに集約できます。今回ご紹介したコードをベースに、ご自身の業務内容に合わせてカスタマイズすることで、さらに効率的なデータ管理が可能になります。
データ集約は、Excelで行われる多くの業務の基本となる作業です。このVBAテクニックを習得することで、日々のルーチンワークから解放され、より付加価値の高い業務に集中できるようになるはずです。ぜひ、 VBAの力を活用して、あなたのExcel作業を次のレベルへと引き上げてください。
