VBAで複数シート間のデータを転記・集約する方法｜For Eachで全シート一括コピー

スポンサーリンク

「Sheet1に入力したデータを、Sheet2にも手で写している」。「月別シートの数字を、毎月集計シートにコピペでまとめている」。こんな作業を繰り返していませんか。

シートが増えるほど、コピペの回数も増えていきます。1枚あたり数分でも、10枚あれば30分以上。しかも貼り間違いや行のズレといったミスもつきまといます。

そんなシート間のデータ転記・集約は、VBAで一瞬で片づけられます。この記事では、コピペでそのまま動く完成コードを3パターン用意しました。単純な別シート転記から、For Eachによる全シート一括集約、条件付き転記までを順番に解説します。

VBAでシート間のデータを転記・集約してできること

まずは、VBAを使うとシート間のデータ処理がどう変わるのかを見ておきましょう。

手作業では、シートを切り替えてコピーして、別のシートに貼り付ける、という操作の繰り返しです。シートが10枚あれば、この往復を10回行うことになります。

VBAなら、この往復をコードに書いておくだけです。マクロを実行すれば、全シートの処理が数秒で終わります。

具体的には、次のような処理を自動化できます。

  • 特定のシートのデータを、別のシートに丸ごと転記する
  • 複数のシートを1枚の集計シートにまとめて集約する
  • 「ステータスが完了の行だけ」のように条件付きで転記する

手作業とVBAの違いを、簡単な表で比べてみます。

項目手作業VBA
10シートの集約約30分数秒
貼り付けミス起きやすい起きない
翌月の再実行また30分ボタン1つ

一度コードを作ってしまえば、翌月以降は実行するだけです。この「作り置き」ができるのがVBAの強みですね。

VBEの起動とマクロを書く準備

コードを書く前に、VBE（Visual Basic Editor）という編集画面を開きます。VBEは、VBAのコードを書くための専用エディタです。

VBEの開き方

VBEを開く方法は2つあります。一番早いのは、Excelの画面で Alt + F11 を押す方法です。これだけでVBEが立ち上がります。

もう1つは、リボンの「開発」タブから開く方法です。「開発」タブの「Visual Basic」ボタンをクリックします。

「開発」タブが見当たらない場合は、表示設定が必要です。次の手順で追加してください。

  1. 「ファイル」→「オプション」を開く
  2. 「リボンのユーザー設定」を選ぶ
  3. 右側の一覧で「開発」にチェックを入れる
  4. 「OK」を押す

これで「開発」タブがリボンに表示されます。

標準モジュールの挿入

VBEが開いたら、コードを書く場所を用意します。メニューの「挿入」→「標準モジュール」を選んでください。

すると「Module1」という白い画面が開きます。ここにコードを書いていきます。VBEの画面構成をもっと詳しく知りたい方は、VBEの画面の見方を図解で解説もあわせてご覧ください。

.xlsmで保存することに注意

マクロを書いたブックは、必ず .xlsm（マクロ有効ブック）形式で保存します。普段の .xlsx 形式では、マクロが保存できません。

.xlsx のまま保存しようとすると、「マクロなしのブックとして保存されます」という警告が出ます。このまま保存すると、せっかく書いたコードが消えてしまいます。

保存時は「名前を付けて保存」で、ファイルの種類を「Excel マクロ有効ブック（.xlsm）」に変えてください。詳しい違いは.xlsxと.xlsmの違いで解説しています。

パターン1：別シートにデータを転記する基本コード

最初は一番シンプルな、別シートへの転記です。「Sheet1のデータを、Sheet2の続きに追記する」という、よくあるパターンから見ていきましょう。

ここでは、Sheet1のA1からC5までのデータを、Sheet2の末尾に追記する例を使います。

Sub データ転記_基本()

    '--- コピー元とコピー先のシートを指定 ---
    Dim wsFrom As Worksheet '転記元シート
    Dim wsTo As Worksheet   '転記先シート
    Set wsFrom = Worksheets("Sheet1")
    Set wsTo = Worksheets("Sheet2")

    '--- 転記先の最終行を取得して、次の行を書き込み位置にする ---
    Dim tRow As Long '転記先の書き込み行
    tRow = wsTo.Cells(Rows.Count, 1).End(xlUp).Row + 1

    '--- A1:C5の範囲を、転記先の末尾に値だけコピー ---
    wsTo.Range("A" & tRow & ":C" & tRow + 4).Value = wsFrom.Range("A1:C5").Value

    MsgBox "転記が完了しました"

End Sub

このコードを書いたら、F5 キーかメニューの「実行」でマクロを動かします。Sheet2の続きにSheet1のデータが追記されれば成功です。

自分の環境に合わせて変えるのは、次の3か所です。

  • Worksheets("Sheet1") … 転記元のシート名
  • Worksheets("Sheet2") … 転記先のシート名
  • Range("A1:C5") … コピーするデータの範囲

コードの仕組みを理解する

このコードのポイントは、別シートを操作するときに必ずシート名を付けている点です。wsFrom.Range(...) のように、どのシートのどの範囲かを明示しています。

シート名を付けずに Range("A1") と書くと、今表示しているシートを指してしまいます。別シート操作では、シートの指定を省略しないのがポイントです。

最終行は Cells(Rows.Count, 1).End(xlUp).Row で取得しています。これは「A列の一番下から上に詰めて、最初にデータがある行」を返す定番のテクニックです。最終行の取得方法はVBA最終行の取得でも詳しく解説しています。

また、コピー先.Value = コピー元.Value のように範囲ごと値を代入しています。セルを1つずつコピーするより、この書き方のほうがずっと高速です。

パターン2：For Eachで全シートを集計シートに集約する

次は、複数のシートを1枚にまとめる集約です。「月別シートを、集計シートに全部まとめたい」というニーズに応えます。

ここでは、「集計」という名前のシートを用意しておきます。そして、それ以外の全シートのデータを、集計シートに順番に追記していきます。

Sub 全シート集約()

    '--- 集約先シートを指定 ---
    Dim wsSum As Worksheet '集約先シート
    Set wsSum = Worksheets("集計")

    '--- 全シートを1枚ずつ処理する ---
    Dim ws As Worksheet '処理中のシート
    For Each ws In Worksheets

        '--- 集計シート自身は処理しない ---
        If ws.Name <> "集計" Then

            '--- 各シートの最終行を取得 ---
            Dim lastRow As Long '各シートのデータ最終行
            lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

            '--- 集約先の書き込み位置（最終行の次）を取得 ---
            Dim tRow As Long '集約先の書き込み行
            tRow = wsSum.Cells(Rows.Count, 1).End(xlUp).Row + 1

            '--- 2行目から最終行までを集約先に転記（1行目は見出しとして除外） ---
            ws.Range("A2:C" & lastRow).Copy wsSum.Range("A" & tRow)

        End If

    Next ws

    MsgBox "全シートの集約が完了しました"

End Sub

このコードを実行すると、集計シート以外の全シートのデータが、集計シートに1つにまとまります。シートが何枚あっても、コードはそのままで動きます。

ちょっとむずかしく見えますが、やっていることはシンプルです。「1枚ずつシートを取り出して、集計シートの末尾に貼る」を全シートで繰り返しているだけです。

コードの仕組みを理解する

ここで使っている For Each ws In Worksheets は、全シートを1つずつ取り出すループです。ws には、処理中のシートが順番に入っていきます。For Eachの基本はVBA For Eachの使い方で解説しています。

処理の流れは次の3ステップです。

  1. For Each で全シートを1枚ずつ取り出す
  2. If ws.Name <> "集計" で集計シート自身を除外する
  3. 各シートの2行目以降を、集計シートの末尾にコピーする

集計シート自身を除外するガードは、必ず入れてください。これがないと、集計シート自身のデータも集計シートに貼り付けてしまいます。

見出し行を除くため、コピー範囲を A2 から始めています。各シートの1行目が見出しの場合、この指定でデータ部分だけをまとめられます。

ここでは書式ごとコピーする Copy を使いました。値だけでよければ、パターン1のように Value での代入のほうが高速です。

パターン3：条件に合うデータだけを別シートに転記する

最後は、条件付きの転記です。「ステータスが完了の行だけ、完了シートに移したい」のように、特定の値の行だけを抜き出します。

ここでは、Sheet1のC列に入った「ステータス」を見て、「完了」の行だけを「完了」シートに転記する例を使います。

Sub 条件付き転記()

    '--- 転記元と転記先のシートを指定 ---
    Dim wsFrom As Worksheet '転記元シート
    Dim wsTo As Worksheet   '転記先シート
    Set wsFrom = Worksheets("Sheet1")
    Set wsTo = Worksheets("完了")

    '--- 転記元の最終行を取得 ---
    Dim lastRow As Long '転記元のデータ最終行
    lastRow = wsFrom.Cells(Rows.Count, 1).End(xlUp).Row

    '--- 転記先の書き込み開始行を取得 ---
    Dim tRow As Long '転記先の書き込み行
    tRow = wsTo.Cells(Rows.Count, 1).End(xlUp).Row + 1

    '--- 2行目から最終行まで1行ずつ判定する ---
    Dim i As Long 'ループ用の行カウンター
    For i = 2 To lastRow

        '--- C列が「完了」の行だけ転記する ---
        If wsFrom.Cells(i, 3).Value = "完了" Then
            wsTo.Range("A" & tRow & ":C" & tRow).Value = _
                wsFrom.Range("A" & i & ":C" & i).Value
            tRow = tRow + 1 '転記したので書き込み行を1つ進める
        End If

    Next i

    MsgBox "条件に合うデータの転記が完了しました"

End Sub

実行すると、C列が「完了」の行だけが、完了シートに集まります。条件を変えたいときは、If wsFrom.Cells(i, 3).Value = "完了" の部分を書き換えます。

たとえば「金額が10000以上の行だけ」にしたいなら、wsFrom.Cells(i, 4).Value >= 10000 のように変えるだけです。

コードの仕組みを理解する

このコードは、1行ずつ条件をチェックしていく作りです。For i = 2 To lastRow で、2行目から最終行までを順番に見ていきます。

ポイントは、転記先の書き込み行 tRow を変数で管理している点です。条件に合う行を転記するたびに、tRow = tRow + 1 で1つ進めます。これで、転記先に隙間なく詰めて書き込めます。

Cells(i, 3) の 3 はC列を意味します。判定する列を変えたいときは、この数字を変えます。1ならA列、2ならB列という対応です。

行と列を数字で指定する Cells の使い方は、VBA RangeとCellsの違いで詳しく解説しています。ループ処理の中では、この Cells が便利です。

VBAのシート転記でよくあるエラーと対処法

シート転記のコードでつまずきやすいエラーを、3つにしぼって紹介します。

実行時エラー9：インデックスが有効範囲にありません

これは、指定したシート名が存在しないときに出るエラーです。原因のほとんどはシート名のタイプミスです。

たとえば、コードでは「集計」と書いているのに、実際のシート名が「集計表」だと、このエラーになります。全角と半角の違い、余分なスペースもよくある原因です。

シート名を確認するには、次のコードが便利です。

Sub シート名を確認()

    Dim ws As Worksheet '確認用のシート
    For Each ws In Worksheets
        Debug.Print ws.Name '各シート名をイミディエイトウィンドウに出力
    Next ws

End Sub

これを実行すると、全シートの正確な名前がイミディエイトウィンドウに表示されます。表示された名前を、コードにそのままコピーすると確実です。

実行時エラー13：型が一致しません

これは、データの型が合っていないときに出ます。たとえば、文字が入ったセルを数値として比較しようとした場合などです。

条件付き転記で「金額が10000以上」と判定するとき、その列に文字列が混ざっているとこのエラーが起きます。判定に使う列には、数値だけが入っているか確認してください。

実行時エラー1004：Rangeの指定が不正

Range指定の書き方が間違っているときや、保護されたシートに書き込もうとしたときに出ます。

転記先シートが保護されていないか、まず確認しましょう。シートの保護がかかっていると、VBAでも書き込めません。

その他のエラーについては、VBAマクロのエラー解決ガイドで頻出エラー別にまとめています。

大量データは画面更新を止めると速い

エラーではありませんが、データ量が多いと処理が遅く感じることがあります。そんなときは、画面更新を一時的に止めると体感速度が上がります。

Sub 高速化のひな形()

    Application.ScreenUpdating = False '画面更新を止める

    '--- ここに転記・集約の処理を書く ---

    Application.ScreenUpdating = True '画面更新を元に戻す

End Sub

処理の前後で画面更新をオフ・オンにするだけです。最後に必ず True へ戻すのを忘れないでください。

まとめ

VBAで複数シート間のデータを転記・集約する方法を、3つのパターンで解説しました。最後にポイントを整理します。

  • パターン1：別シートへの単純転記は、シート名を明示して Value で範囲ごと代入する
  • パターン2：全シート集約は For Each でループし、集計シート自身を除外する
  • パターン3：条件付き転記は1行ずつ判定し、書き込み行を変数で管理する
  • 別シート操作では、必ずシート名を付ける
  • マクロを書いたら .xlsm 形式で保存する

まずはパターン1から、自分の業務のシート名に書き換えて試してみてください。動くコードが1つできれば、あとは応用が効きます。

ループ処理をもっと深く知りたい方はVBA For~Next文の使い方を、VBAで業務全体を自動化したい方はExcel VBAで仕事を自動化する方法もあわせてご覧ください。

スポンサーリンク
タイトルとURLをコピーしました