Excel業務では、「このシートのデータを別シートへコピーする」「集計用ブックへ毎日データを転記する」といった作業が頻繁に発生します。 件数が少ないうちは手作業でも対応できますが、毎日同じ範囲をコピー＆ペーストしていると、作業時間が積み重なるだけでなく、 貼り付け先の間違い・行の抜け・コピー範囲のズレといったミスも起こりやすくなります。
そこで今回は、VBAを使って別シートへの転記、別ブックへの転記、大量データを高速に転記する方法をまとめて解説します。 前回の記事で扱った「最終行取得」の考え方も組み込み、データ件数が毎回変わる実務でもそのまま応用できる構成にしています。
特に重要なのは、単純にCopyメソッドを使うのではなく、
Range同士を直接値代入する方法です。
書式やクリップボードを介さずデータだけを一括転記できるため、処理が速く、安定したマクロを作れます。
【結論】実務でそのまま使える！汎用データ転記マクロ
まずは、実務で使いやすい汎用的な転記マクロを紹介します。 以下のコードは、転記元シートのA列から最終行を取得し、A列～F列のデータを別シートへ一括転記します。
さらに、DEST_BOOK_PATHにExcelファイルのフルパスを設定すると、
別ブックへの転記にもそのまま対応できます。
空文字のままにしておけば、同じブック内の別シートへ転記します。
'==================================================
' 機能：別シート・別ブックへデータを一括転記する
' A列の最終行を取得し、A2:F最終行を転記する
'==================================================
Option Explicit
Sub TransferData()
'------------------------------
' 設定値
'------------------------------
Const SOURCE_SHEET As String = "データ"
Const DEST_SHEET As String = "転記先"
' 同じブック内へ転記する場合は空文字にする
' 別ブックの場合：
' 例）C:\Users\user\Desktop\集計.xlsx
Const DEST_BOOK_PATH As String = ""
'------------------------------
' 変数宣言
'------------------------------
Dim srcWb As Workbook
Dim destWb As Workbook
Dim srcWs As Worksheet
Dim destWs As Worksheet
Dim srcRange As Range
Dim destRange As Range
Dim lastRow As Long
Dim rowCount As Long
Dim openedByMacro As Boolean
' エラーが発生した場合はErrHandlerへ移動する
On Error GoTo ErrHandler
'------------------------------
' 高速化設定
'------------------------------
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' このマクロを保存しているブックを転記元にする
Set srcWb = ThisWorkbook
' 転記元シートが存在するか確認する
If Not SheetExists(SOURCE_SHEET, srcWb) Then
Err.Raise vbObjectError + 1000, , _
"転記元シート「" & SOURCE_SHEET & "」が見つかりません。"
End If
Set srcWs = srcWb.Worksheets(SOURCE_SHEET)
'------------------------------
' 転記先ブックを決定する
'------------------------------
If DEST_BOOK_PATH = "" Then
' パスが空なら同じブック内へ転記
Set destWb = srcWb
Else
' 指定ファイルが存在するか確認する
If Dir(DEST_BOOK_PATH) = "" Then
Err.Raise vbObjectError + 1001, , _
"転記先ブックが見つかりません。" & vbCrLf & DEST_BOOK_PATH
End If
' 外部ブックを開く
Set destWb = Workbooks.Open(Filename:=DEST_BOOK_PATH)
openedByMacro = True
End If
' 転記先シートが存在するか確認する
If Not SheetExists(DEST_SHEET, destWb) Then
Err.Raise vbObjectError + 1002, , _
"転記先シート「" & DEST_SHEET & "」が見つかりません。"
End If
Set destWs = destWb.Worksheets(DEST_SHEET)
'------------------------------
' A列の最終行を取得する
'------------------------------
lastRow = srcWs.Cells(srcWs.Rows.Count, "A").End(xlUp).Row
' 1行目しか存在しない場合は転記対象なし
If lastRow < 2 Then
MsgBox "転記対象のデータがありません。", vbInformation
GoTo Finally
End If
' 転記する行数を計算する
rowCount = lastRow - 1
' A2:F最終行を転記元範囲として取得
Set srcRange = srcWs.Range( _
srcWs.Cells(2, 1), _
srcWs.Cells(lastRow, 6))
' 転記元と同じサイズの転記先範囲を作成
Set destRange = destWs.Range( _
destWs.Cells(2, 1), _
destWs.Cells(rowCount + 1, 6))
'------------------------------
' Copyを使わず値を一括転記する
'------------------------------
destRange.Value = srcRange.Value
' 別ブックへ転記した場合は保存する
If openedByMacro Then
destWb.Save
End If
MsgBox rowCount & "件のデータを転記しました。", vbInformation
Finally:
'------------------------------
' 必ず高速化設定を元に戻す
'------------------------------
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
' マクロ自身が開いた外部ブックだけ閉じる
If openedByMacro Then
If Not destWb Is Nothing Then
destWb.Close SaveChanges:=True
End If
End If
Set srcRange = Nothing
Set destRange = Nothing
Set srcWs = Nothing
Set destWs = Nothing
Set srcWb = Nothing
Set destWb = Nothing
Exit Sub
ErrHandler:
MsgBox "エラーが発生しました。" & vbCrLf & _
"エラー番号：" & Err.Number & vbCrLf & _
"内容：" & Err.Description, _
vbExclamation
Resume Finally
End Sub
'==================================================
' 機能：指定したブックにシートが存在するか確認する
'==================================================
Private Function SheetExists( _
ByVal sheetName As String, _
ByVal wb As Workbook) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
On Error GoTo 0
SheetExists = Not ws Is Nothing
End Functionコードを使う前に変更する場所
まず、コード冒頭にある次の3つの設定を自分のExcelファイルに合わせて変更してください。
| 設定項目 | 意味 | 設定例 |
|---|---|---|
| SOURCE_SHEET | 転記元シート名 | データ |
| DEST_SHEET | 転記先シート名 | 転記先 |
| DEST_BOOK_PATH | 別ブックへ転記する場合のフルパス。同一ブックなら空文字 | C:\Work\集計.xlsx |
また、サンプルではA列を基準に最終行を取得し、A列～F列を転記しています。 実際のデータ範囲に合わせて、基準列や列数を変更してください。
コードの詳しい解説
1. 最終行を取得して、データ件数の変化に対応する
今回のマクロでは、次の処理でA列の最終データ行を取得しています。
Dim lastRow As Long
' A列の一番下から上方向へ検索し、最後のデータ行を取得する
lastRow = srcWs.Cells(srcWs.Rows.Count, "A").End(xlUp).Row
Rows.Countでワークシートの最終行番号を取得し、
そこからEnd(xlUp)を使って上方向へ移動することで、
A列で最後に値が入っているセルを取得しています。
たとえば今日は100行、翌日は5,000行というようにデータ量が変わっても、 毎回自動的に実データの最終行まで転記できるのがポイントです。
最終行取得そのものを詳しく確認したい場合は、 VBAで最終行・最終列を取得する4つの方法 もあわせて確認してください。
2. RangeとCellsを組み合わせて転記範囲を指定する
今回のコードでは、転記元範囲を次のように作成しています。
Set srcRange = srcWs.Range( _
srcWs.Cells(2, 1), _
srcWs.Cells(lastRow, 6))
Cells(2, 1)は「2行目・1列目」、つまりA2セルを表します。
Cells(lastRow, 6)は「最終行・6列目」、つまりF列の最終行です。
この2つのセルをRangeの開始セル・終了セルとして指定することで、
A2:F最終行という可変範囲を作れます。
3. Copyメソッドを使わず、Valueで直接転記する
今回の転記処理で最も重要なのが、次の1行です。
' 転記先へ値をまとめて代入する
destRange.Value = srcRange.Value
VBA初心者の場合、転記と聞くと次のようなCopyを思い浮かべるかもしれません。
' Copyメソッドを使う方法
srcRange.Copy Destination:=destRange
もちろんこの方法でも転記できますが、値だけを転記したい場合には、
destRange.Value = srcRange.Valueの方がシンプルで高速です。
直接値代入には、主に次のメリットがあります。
- Excelのクリップボードを使用しない
- コピー中の点線表示が発生しない
- セルの選択やアクティブ化が不要
- 大量データでも一括して値を受け渡せる
- 書式を誤って上書きするリスクを減らせる
特に、転記先にあらかじめ罫線・色・表示形式などを設定している帳票では、 値だけを転記できる直接代入が非常に使いやすいです。
4. Worksheetsでシートを明示的に指定する
シートを取得するときは、次のようにWorksheetsを使用しています。
Set srcWs = srcWb.Worksheets(SOURCE_SHEET)
Set destWs = destWb.Worksheets(DEST_SHEET)
Worksheets("シート名")のように指定すると、
対象ブック内の特定ワークシートを取得できます。
実務では、Range("A1")だけを書くのではなく、
どのブック・どのシートのRangeなのかを明示することが重要です。
ブックやシートを省略すると、現在アクティブになっているブックやシートを対象として処理することがあり、 意図しない場所へデータを書き込む原因になります。
5. Workbooks.Openで別ブックを開く
別ファイルへ転記する場合は、Workbooks.Openを使用します。
Set destWb = Workbooks.Open(Filename:=DEST_BOOK_PATH)Filenameには、開きたいExcelファイルのフルパスを指定します。 たとえば次のようなパスです。
Const DEST_BOOK_PATH As String = _
"C:\Work\集計.xlsx"
ファイルが存在しない状態でWorkbooks.Openを実行するとエラーになるため、
サンプルコードでは事前にDir関数を使ってファイルの存在確認を行っています。
6. Closeで外部ブックを安全に閉じる
別ブックへの処理が終わったら、必要に応じて保存して閉じます。
' 転記内容を保存する
destWb.Save
' 保存してブックを閉じる
destWb.Close SaveChanges:=True
CloseメソッドのSaveChanges引数にTrueを指定すると、
変更内容を保存してからブックを閉じます。
一方、保存せず閉じたい場合はFalseを指定します。
重要な集計ブックを扱う場合には、意図せず保存・上書きしないよう、
SaveChangesの指定を明示することをおすすめします。
使用している主要なオブジェクト・プロパティ・メソッド
| 名称 | 役割 | 注意点 |
|---|---|---|
| Workbook | Excelブックを表すオブジェクト | ThisWorkbookとActiveWorkbookの違いに注意 |
| Worksheet | ワークシートを表すオブジェクト | シート名が違うと実行時エラーになる |
| Worksheets | ブック内のワークシート一覧を扱うコレクション | 対象Workbookを明示すると安全 |
| Range | セルまたはセル範囲を表す | ブック・シートを省略しない方が安全 |
| Cells | 行番号・列番号でセルを指定する | Cells(行, 列)の順番 |
| Value | セルの値を取得・設定する | 転記元・転記先の範囲サイズを合わせる |
| Workbooks.Open | 外部Excelファイルを開く | Filenameに正しいパスが必要 |
| Workbook.Close | ブックを閉じる | SaveChangesで保存有無を明示する |
| End(xlUp) | 下端から上方向へ移動して最終データセルを取得 | 基準列に必ず値が入るデータ構造で使う |
【爆速化】数万件でも一瞬！転記処理を劇的に高速化する3つのテクニック
数十行程度なら処理速度を気にする必要はほとんどありませんが、 数万行、数十万セルを扱うようになると、VBAの書き方によって処理時間に大きな差が出ます。
大量データを扱う場合は、次の3つを意識してください。
- 画面更新を停止する
- 自動計算を停止する
- セルを1つずつ処理せず、配列やRangeで一括処理する
① Application.ScreenUpdating = Falseで画面更新を止める
Excelはセルを書き換えるたびに、画面表示も更新しようとします。 大量のセルを処理すると、この画面描画が大きな負荷になります。
そこで、処理開始時に次の設定を行います。
' 画面更新を停止する
Application.ScreenUpdating = False
'==================================================
' ここで転記などの処理を実行する
'==================================================
' 処理終了後は必ず元に戻す
Application.ScreenUpdating = True画面更新を止めてもセルへの書き込み自体は実行されます。 ユーザーから見ると途中経過が表示されなくなるため、 無駄な画面描画を減らして処理速度を向上できます。
② Application.Calculationで自動計算を停止する
数式が多いブックでは、セルを書き換えるたびに再計算が実行されることがあります。 そのため、大量転記では計算処理が大きなボトルネックになる場合があります。
' 自動計算を一時的に停止する
Application.Calculation = xlCalculationManual
'==================================================
' ここで大量データの転記処理を実行する
'==================================================
' 処理終了後は自動計算へ戻す
Application.Calculation = xlCalculationAutomatic特にVLOOKUP、XLOOKUP、SUMIFSなどの数式が大量に存在するブックでは効果が出やすい方法です。
ただし、後述するように処理終了後は必ずxlCalculationAutomaticへ戻す必要があります。
③ 配列を使って一括転記する
VBAで大量データを扱うときに、避けたいのがセルを1件ずつ読み書きする処理です。
たとえば、次のようなループは件数が増えるほど遅くなります。
Dim i As Long
' セルを1つずつ読み書きするため、大量データでは遅くなりやすい
For i = 2 To 50000
Worksheets("転記先").Cells(i, 1).Value = _
Worksheets("データ").Cells(i, 1).Value
Next i高速化したい場合は、Rangeのデータを一度Variant型の配列へ取り込み、 配列全体をまとめて転記先へ書き戻します。
配列を使った高速転記の完全サンプル
'==================================================
' 機能：配列を使って大量データを高速転記する
'==================================================
Sub TransferDataByArray()
Dim srcWs As Worksheet
Dim destWs As Worksheet
Dim lastRow As Long
Dim dataArray As Variant
On Error GoTo ErrHandler
' 高速化設定
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' 対象シートを設定する
Set srcWs = ThisWorkbook.Worksheets("データ")
Set destWs = ThisWorkbook.Worksheets("転記先")
' A列を基準に最終行を取得する
lastRow = srcWs.Cells(srcWs.Rows.Count, "A").End(xlUp).Row
' データが存在しない場合は終了する
If lastRow < 2 Then
MsgBox "転記対象のデータがありません。", vbInformation
GoTo Finally
End If
' A2:F最終行をVariant配列へ一括で読み込む
dataArray = srcWs.Range( _
srcWs.Cells(2, 1), _
srcWs.Cells(lastRow, 6)).Value
' 配列全体を転記先へ一括で書き込む
destWs.Cells(2, 1).Resize( _
UBound(dataArray, 1), _
UBound(dataArray, 2)).Value = dataArray
MsgBox UBound(dataArray, 1) & _
"件のデータを転記しました。", vbInformation
Finally:
' 高速化設定を必ず元に戻す
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Set srcWs = Nothing
Set destWs = Nothing
Exit Sub
ErrHandler:
MsgBox "エラーが発生しました。" & vbCrLf & _
"エラー番号：" & Err.Number & vbCrLf & _
"内容：" & Err.Description, _
vbExclamation
Resume Finally
End Subなぜ配列を使うと速いのか？
Excel VBAでは、ワークシート上のセルへアクセスする処理そのものに一定のコストがあります。 そのため、5万行のデータを1セルずつ読み込むと、 VBAとExcelワークシートの間で何万回もデータの受け渡しが発生します。
一方、次の処理では範囲全体をまとめて配列へ取り込めます。
' Rangeのデータを配列へ一括取得する
dataArray = srcWs.Range( _
srcWs.Cells(2, 1), _
srcWs.Cells(lastRow, 6)).Valueそして書き込みも一括です。
' 配列をまとめてセルへ書き戻す
destWs.Cells(2, 1).Resize( _
UBound(dataArray, 1), _
UBound(dataArray, 2)).Value = dataArray「セルを1個ずつ触らない」ことが、大量データ処理を高速化する基本だと覚えておきましょう。
Resizeメソッドの使い方
Resizeは、基準セルから指定した行数・列数へ範囲を広げるためのプロパティです。
基本形は次のとおりです。
' 基準セル.Resize(行数, 列数)
destWs.Cells(2, 1).Resize(100, 6)この例では、A2セルを起点として100行×6列、つまりA2:F101の範囲を取得します。
配列転記ではUBoundを使い、配列の行数・列数に合わせて転記先範囲を自動調整しています。
UBound関数の引数
UBoundは、配列の最大インデックスを取得する関数です。
ExcelのRangeを配列へ代入すると通常は2次元配列になるため、次のように使います。
- UBound(dataArray, 1)：配列の行方向の最大要素数を取得
- UBound(dataArray, 2)：配列の列方向の最大要素数を取得
これをResizeと組み合わせれば、
データ件数や列数を固定せずに一括転記できます。
実務でよくあるエラーと注意点
1. 「インデックスが有効範囲にありません」エラー
シート名を間違えたときによく発生するのが、 実行時エラー9「インデックスが有効範囲にありません」です。
たとえば、実際のシート名が「売上データ」なのに、コードで次のように指定するとエラーになります。
' 「データ」というシートが存在しなければエラーになる
Set srcWs = ThisWorkbook.Worksheets("データ")
記事冒頭の汎用コードでは、SheetExists関数を使い、
処理前にシートの存在をチェックしています。
特に複数人で使うExcelでは、利用者がシート名を変更する可能性もあるため、 存在チェックを入れておくとトラブルの原因を特定しやすくなります。
2. 別ブックのファイルパスが間違っている
Workbooks.Openで指定したファイルが存在しない場合もエラーになります。
そのため、今回のコードではDir関数を使っています。
If Dir(DEST_BOOK_PATH) = "" Then
MsgBox "ファイルが見つかりません。"
End If
Dirへファイルパスを渡し、対象が存在しなければ空文字が返ります。
共有フォルダやネットワークドライブ上のファイルを扱う場合は、 ファイルの存在だけでなく、アクセス権限やネットワーク接続状態にも注意してください。
3. ScreenUpdatingをFalseのままにしない
高速化のために次の設定を使う場合、 処理終了時に必ず元へ戻してください。
' 処理開始
Application.ScreenUpdating = False
' 処理終了
Application.ScreenUpdating = True
Falseのまま処理が終了すると、その後のExcel操作でも画面が正常に更新されないことがあります。
4. CalculationをManualのままにしない
さらに注意したいのが、自動計算の設定です。
' 高速化のため一時的に手動計算へ変更
Application.Calculation = xlCalculationManual
' 処理終了後は必ず自動計算へ戻す
Application.Calculation = xlCalculationAutomatic
xlCalculationManualのまま終了してしまうと、
ユーザーがセルの値を変更しても数式が自動再計算されなくなる可能性があります。
「Excelの数字が更新されない」という非常に分かりにくいトラブルにつながるため、 正常終了時だけでなく、エラー発生時にも設定を元へ戻す設計が重要です。
今回の汎用コードでは、正常終了・エラー終了のどちらからもFinally:へ移動し、
画面更新と計算方法を元へ戻す構造にしています。
5. 転記元と転記先のRangeサイズを合わせる
Range同士を直接代入するときは、 転記元と転記先の行数・列数を同じサイズにすることが基本です。
たとえば転記元が100行×6列なら、転記先も100行×6列にします。
今回のコードでは次のように行数を計算しているため、 最終行が変わっても転記元と転記先のサイズが一致します。
' 2行目から最終行までの件数
rowCount = lastRow - 1
' 転記元
Set srcRange = srcWs.Range( _
srcWs.Cells(2, 1), _
srcWs.Cells(lastRow, 6))
' 転記先も同じ行数・列数にする
Set destRange = destWs.Range( _
destWs.Cells(2, 1), _
destWs.Cells(rowCount + 1, 6))6. ThisWorkbookとActiveWorkbookを混同しない
今回のコードでは、転記元ブックとしてThisWorkbookを使用しています。
ThisWorkbookは「現在実行しているVBAコードが保存されているブック」です。 一方、ActiveWorkbookは「現在画面上でアクティブになっているブック」です。
別ブックを開くマクロではアクティブブックが途中で変わる可能性があるため、
実務ではThisWorkbookやWorkbook変数を使って、
処理対象のブックを明示的に指定することをおすすめします。
転記処理を作るときの実務向けおすすめパターン
最後に、データ量や目的に応じた使い分けを整理します。
| 目的 | おすすめ方法 |
|---|---|
| 値だけを別シートへコピー | Range.Value = Range.Value |
| 別ブックへデータを保存 | Workbooks.Open + Range.Value + Save + Close |
| 数万～数十万セルを高速処理 | 配列へ一括取得して一括書き込み |
| 書式ごとコピーしたい | Copyメソッドを検討 |
| 大量データで数式が多い | ScreenUpdatingとCalculationの一時停止 |
単純な値転記であれば、まずはRange同士の直接代入を基本形として覚えておくとよいでしょう。 そのうえで、途中でデータを加工・判定する必要が出てきた場合には、配列処理へ発展させると効率的です。
まとめ
今回は、VBAを使って別シート・別ブックへデータを転記する方法と、 大量データを高速に処理するためのテクニックを解説しました。
- 最終行を自動取得すれば、毎回データ件数が変わっても対応できる
- 値だけの転記ならdestRange.Value = srcRange.Valueが高速で安全
- 別ブックはWorkbooks.Open → 転記 → Save → Closeの流れで操作する
- Application.ScreenUpdating = Falseで画面描画を止めて高速化する
- Application.Calculation = xlCalculationManualで不要な再計算を抑える
- 数万件以上の処理では、セルをループせず配列で一括処理する
- 高速化設定は、エラーが発生しても必ず元へ戻す
- シート名・ファイルパスの存在確認を入れると、実務で使いやすいマクロになる
転記マクロを安定して動かすうえで、今回使用した「最終行を正しく取得する処理」は非常に重要です。 まだ最終行取得に自信がない場合は、 VBAで最終行・最終列を取得する4つの方法 もあわせて確認しておくと、データ件数が変化するさまざまな業務へ応用しやすくなります。
「最終行を取得する → 必要な範囲を決める → Rangeまたは配列で一括処理する」 という流れを身につければ、日々の手作業によるコピー＆ペーストを、速く正確なVBA処理へ置き換えられます。
次のステップでは、フォルダ内の複数Excelファイルを一括結合！Dir関数で高速自動集約する方法はこちらを学ばれることをお勧めします。
