Excelで同じ形式のデータを何度もコピーして貼り付けていると、作業に時間がかかるだけでなく、貼り付け先を間違える可能性も高くなります。
例えば、次のような作業です。
- 一覧表のデータを1行ずつ別シートへ転記する
- 指定したセル範囲を複数箇所へコピーする
- 複数のシートから同じ位置のデータを集める
- 条件に合う行だけ別の表へ貼り付ける
このような繰り返し作業は、VBAのFor文やFor Each文とコピー処理を組み合わせることで自動化できます。
ただし、単純にCopyとPasteを繰り返すだけでは、処理が遅くなったり、意図しない場所へ貼り付けたりすることがあります。実務で使う場合は、コピー元と貼り付け先を明示し、処理内容に応じてCopyメソッドと値の直接代入を使い分けることが重要です。
この記事では、VBAでコピーと貼り付けを繰り返す基本から、複数行を別シートへ安全に転記する方法まで解説します。
✅ VBAでコピーと貼り付けを繰り返す基本の考え方
VBAで繰り返しコピーする場合は、主に次の3つを決めます。
- どの範囲をコピーするか
- 何回繰り返すか
- どこへ貼り付けるか
例えば、元データの2行目から10行目までを順番にコピーする場合は、行番号をFor文で変化させます。
Dim sourceRow As Long
For sourceRow = 2 To 10
' sourceRowを使ってコピー元を切り替える
Next sourceRow
繰り返し処理の中でコピー元と貼り付け先の行番号を指定することで、同じ操作を複数行へ適用できます。
・コピー元と貼り付け先を変数で管理する
実務コードでは、Range("A2")のようなセル番地を各所へ直接書き続けるより、対象シートや行番号を変数で管理した方が安全です。
例えば、次のような役割を分けて考えます。
sourceWorksheet：コピー元のシートdestinationWorksheet：貼り付け先のシートsourceRow：現在コピーしている行destinationRow：現在の貼り付け先の行
変数名から役割が分かるようにしておくと、あとから対象シートや転記位置を変更するときにも修正しやすくなります。
✅ For文で複数行を別シートへコピーする方法
ここでは、「元データ」シートのA列からC列までを、「転記先」シートへ1行ずつコピーする例を紹介します。
コピー元の2行目から最終行までを順番に処理し、転記先では2行目からデータを並べます。
・後から転記範囲を変更しやすい形で設計する
単純なサンプルでは、コピー元や貼り付け先にSelectやActivateを使うことがあります。
しかし、アクティブなシートへ依存すると、マクロ実行中に別のシートが選択された場合、意図しないセルを操作する危険があります。
そこで、次のコードではコピー元と貼り付け先をWorksheet型の変数へ設定し、対象を明示して処理します。
Option Explicit
Sub CopyRowsToAnotherSheet()
Dim sourceWorksheet As Worksheet
Dim destinationWorksheet As Worksheet
Dim lastSourceRow As Long
Dim sourceRow As Long
Dim destinationRow As Long
Set sourceWorksheet = ThisWorkbook.Worksheets("元データ")
Set destinationWorksheet = ThisWorkbook.Worksheets("転記先")
' コピー元のA列を基準に最終行を取得する
lastSourceRow = sourceWorksheet.Cells( _
sourceWorksheet.Rows.Count, "A" _
).End(xlUp).Row
' 転記先は2行目から書き込みを開始する
destinationRow = 2
For sourceRow = 2 To lastSourceRow
' A列からC列までを、転記先の同じ列構成へコピーする
sourceWorksheet.Range( _
sourceWorksheet.Cells(sourceRow, "A"), _
sourceWorksheet.Cells(sourceRow, "C") _
).Copy Destination:=destinationWorksheet.Cells(destinationRow, "A")
destinationRow = destinationRow + 1
Next sourceRow
' コピー状態を解除する
Application.CutCopyMode = False
End Sub
・この書き方ならシートを切り替えずに処理できる
このコードでは、SelectやActivateを使わず、すべてのセル参照にシートを明示しています。
そのため、マクロ実行時にどのシートが表示されていても、コピー元は「元データ」、貼り付け先は「転記先」に固定されます。
また、コピー元の最終行を自動的に取得しているため、データ件数が増減してもループ回数を手作業で変更する必要がありません。
処理の流れは次のとおりです。
- コピー元と貼り付け先のシートを取得する
- コピー元の最終行を確認する
- 2行目から最終行まで順番に処理する
- A列からC列までを貼り付け先へコピーする
- 貼り付け先の行番号を1つ進める
1行ずつ処理する構成にしているため、後から条件判定や加工処理を追加しやすい点もメリットです。
コピー後にセルの点線表示を消したい場合や、コピー状態を確実に解除したい場合は、Application.CutCopyModeの使い方もあわせて確認してみてください。⇒【VBA】Application.CutCopyModeの使い方｜コピー・カットモードを解除する方法
✅ Copy Destinationを使うメリット
VBAでは、次のようにコピーと貼り付けを別々に書く方法もあります。
sourceWorksheet.Range("A2:C2").Copy
destinationWorksheet.Range("A2").PasteSpecial
一方、前述のコードではCopyメソッドのDestination引数を使っています。
sourceRange.Copy Destination:=destinationRange
この書き方では、コピー元と貼り付け先を1つの処理として指定できます。
PasteSpecialを使う方法と比べてコードが短くなり、貼り付け先の指定漏れも防ぎやすくなります。
ただし、Copy Destinationでは、通常は値だけでなく数式や書式なども一緒にコピーされます。値だけを転記したい場合は、コピー操作を使わず、Valueプロパティで直接代入する方が適しています。
値だけ、書式だけ、数式だけなど、貼り付ける内容を細かく指定したい場合は、PasteSpecialメソッドを使う方法が適しています。具体的な使い方は【VBA】Excel VBAのPasteSpecialメソッドの活用方法で詳しく解説しています。
✅ 値だけを繰り返し貼り付ける方法
書式や数式を引き継ぐ必要がなく、表示されている値だけを転記したい場合は、次のように記述できます。
destinationWorksheet.Cells(destinationRow, "A").Resize(1, 3).Value = _
sourceWorksheet.Cells(sourceRow, "A").Resize(1, 3).Value
この方法はクリップボードを使わないため、Copyメソッドを繰り返すより高速に処理できる場合があります。
また、コピー状態が残らないため、Application.CutCopyMode = Falseも必要ありません。
ただし、数式を値として固定したくない場合や、セルの背景色、罫線、表示形式まで引き継ぎたい場合には向いていません。
何を貼り付けたいのかに応じて、次のように使い分けます。
- 値・数式・書式をまとめて複製する：
Copy Destination - 値だけを高速に転記する：
Valueの直接代入 - 書式や数式などを選んで貼り付ける：
PasteSpecial
値を直接代入するときは、ValueプロパティとTextプロパティの違いを理解しておくことも重要です。セルから取得できる内容や使い分けについては、【VBA】ValueプロパティとTextプロパティの違いとは？取得値と使用場面を解説をご覧ください。
✅ 条件に合う行だけコピーして貼り付ける方法
実務では、すべての行をそのまま転記するのではなく、条件に合うデータだけを別シートへコピーしたい場面がよくあります。
例えば、次のようなケースです。
- ステータスが「完了」の行だけ転記する
- 金額が一定以上のデータだけ抽出する
- 担当者名が入力されている行だけ集める
- エラーや未処理のデータだけ別表へまとめる
この場合は、For文の中へIf文を組み込み、条件に一致した行だけコピーします。
・条件判定と転記処理を分けて読みやすくする
次の例では、「元データ」シートのD列が「完了」の行だけを「転記先」シートへコピーします。
「完了」のような文字列を正確に判定する場合は、【VBA】文字列の完全一致の処理（IF文）｜実務で壊れない条件分岐の基本設計もあわせて理解しておくと安心です。
Option Explicit
Sub CopyCompletedRows()
Dim sourceWorksheet As Worksheet
Dim destinationWorksheet As Worksheet
Dim lastSourceRow As Long
Dim sourceRow As Long
Dim destinationRow As Long
Dim processingStatus As String
Set sourceWorksheet = ThisWorkbook.Worksheets("元データ")
Set destinationWorksheet = ThisWorkbook.Worksheets("転記先")
' コピー元のA列を基準に最終行を取得する
lastSourceRow = sourceWorksheet.Cells( _
sourceWorksheet.Rows.Count, "A" _
).End(xlUp).Row
' 転記先の次の空き行を取得する
destinationRow = destinationWorksheet.Cells( _
destinationWorksheet.Rows.Count, "A" _
).End(xlUp).Row + 1
For sourceRow = 2 To lastSourceRow
' 判定に使う値を先に変数へ保存する
processingStatus = Trim$(CStr( _
sourceWorksheet.Cells(sourceRow, "D").Value _
))
If processingStatus = "完了" Then
' 条件に一致した行だけA列からD列までコピーする
sourceWorksheet.Range( _
sourceWorksheet.Cells(sourceRow, "A"), _
sourceWorksheet.Cells(sourceRow, "D") _
).Copy Destination:=destinationWorksheet.Cells(destinationRow, "A")
destinationRow = destinationRow + 1
End If
Next sourceRow
Application.CutCopyMode = False
End Sub
このコードでは、判定に使用するD列の値をprocessingStatusへ保存してから条件を確認しています。
セルを直接何度も参照するよりも、何を判定しているのかが分かりやすく、後から条件を変更しやすい構成です。
また、条件に一致した場合だけdestinationRowを増やしています。これにより、転記先に空白行を作らず、対象データだけを連続して並べられます。
条件に一致するデータだけを別シートへ取り出したい場合は、【VBA】値を抽出する方法｜条件・範囲・高速化まで実務で使える完全ガイドも参考になります。
✅ 空白行を飛ばしてコピーする方法
元データの途中に空白行が含まれている場合、そのまま処理すると、不要な空白まで転記されることがあります。
A列が空白の行を対象外にする場合は、If文で確認してからコピーします。
If Len(Trim$(CStr(sourceWorksheet.Cells(sourceRow, "A").Value))) > 0 Then
sourceWorksheet.Range( _
sourceWorksheet.Cells(sourceRow, "A"), _
sourceWorksheet.Cells(sourceRow, "C") _
).Copy Destination:=destinationWorksheet.Cells(destinationRow, "A")
destinationRow = destinationRow + 1
End If
Len関数で文字数を確認し、0より大きい場合だけ転記しています。
単純に次のように書くこともできます。
If sourceWorksheet.Cells(sourceRow, "A").Value <> "" Then
ただし、セルに半角スペースだけが入力されている場合は、空白として扱われません。
そのため、実務ではTrim$とCStrを組み合わせ、前後の空白を除去してから判定すると安全です。
なお、A列が空白でもB列やC列にデータが入る可能性がある場合は、A列だけを基準にしてよいかを確認する必要があります。業務データの構造に合わせて、空白判定に使う列を決めましょう。
空白行ではコピー処理を行わず、次の行へ進めたい場合は、【VBA】IF文で空白なら次の処理をさせる方法｜スキップ処理と安全な条件分岐を実務目線で解説も確認してみてください。
データの途中に空白行があっても、最終行まで処理を続けたい場合は、【VBA】For文で空白行まで繰り返す方法｜安全な実務設計も参考になります。
✅ 貼り付け先の最終行へ繰り返し追記する方法
転記先に既存データがある場合、毎回2行目から貼り付けると、すでにあるデータを上書きしてしまいます。
そのため、既存データの続きへ追加したい場合は、貼り付け先の最終行を取得して、その次の行から処理を開始します。
destinationRow = destinationWorksheet.Cells( _
destinationWorksheet.Rows.Count, "A" _
).End(xlUp).Row + 1
例えば、転記先のA列に10行目までデータがあれば、destinationRowには11が入ります。
ただし、見出しだけがあり、2行目以降が空白の場合も考慮する必要があります。
・見出し行だけの場合にも対応する
より安全にする場合は、次のように書けます。
Dim lastDestinationRow As Long
lastDestinationRow = destinationWorksheet.Cells( _
destinationWorksheet.Rows.Count, "A" _
).End(xlUp).Row
If lastDestinationRow < 2 Then
destinationRow = 2
Else
destinationRow = lastDestinationRow + 1
End If
見出しが1行目にある前提で、最終行が2未満なら2行目から開始します。
貼り付け先の開始位置を固定値だけで決めず、既存データの状態に応じて取得することで、上書き事故を防ぎやすくなります。
✅ 複数シートからデータを繰り返し集める方法
月別シートや担当者別シートなど、複数のワークシートからデータをまとめたい場合は、For Each文を使うと便利です。
次の例では、「集計」シート以外のすべてのシートから、A2:C最終行までを集計シートへ順番にコピーします。
・対象外のシートを明示して誤転記を防ぐ
Option Explicit
Sub CollectDataFromWorksheets()
Dim sourceWorksheet As Worksheet
Dim summaryWorksheet As Worksheet
Dim lastSourceRow As Long
Dim destinationRow As Long
Dim sourceRange As Range
Set summaryWorksheet = ThisWorkbook.Worksheets("集計")
destinationRow = summaryWorksheet.Cells( _
summaryWorksheet.Rows.Count, "A" _
).End(xlUp).Row + 1
For Each sourceWorksheet In ThisWorkbook.Worksheets
' 集計シート自身は処理対象から除外する
If sourceWorksheet.Name <> summaryWorksheet.Name Then
lastSourceRow = sourceWorksheet.Cells( _
sourceWorksheet.Rows.Count, "A" _
).End(xlUp).Row
' データ行が存在するシートだけ処理する
If lastSourceRow >= 2 Then
Set sourceRange = sourceWorksheet.Range( _
sourceWorksheet.Cells(2, "A"), _
sourceWorksheet.Cells(lastSourceRow, "C") _
)
sourceRange.Copy _
Destination:=summaryWorksheet.Cells(destinationRow, "A")
destinationRow = destinationRow + sourceRange.Rows.Count
End If
End If
Next sourceWorksheet
Application.CutCopyMode = False
End Sub
このコードでは、1行ずつコピーするのではなく、各シートのデータ範囲をまとめてコピーしています。
行単位の細かな条件判定が不要であれば、範囲を一括コピーした方がコードが短くなり、処理速度も向上しやすくなります。
一方、各行を確認してから転記したい場合は、シートごとのループの中へ、さらに行単位のFor文を追加します。
ただし、ループが二重になると処理内容が分かりにくくなるため、シート単位の処理を別プロシージャへ分ける設計も有効です。
シート内のデータだけでなく、複数のシートそのものをまとめてコピーしたい場合は、【Excel】複数シートを一括コピーする方法｜連続選択・別ブックへの複製まで解説も参考になります。
✅ コピー処理を高速化するポイント
データ量が少ない場合は、1行ずつCopyしても大きな問題はありません。
しかし、数千行以上のデータを繰り返し処理すると、実行時間が長くなることがあります。
その場合は、次の点を見直します。
・値だけならまとめて代入する
値だけを転記する場合は、コピー元と貼り付け先の範囲サイズをそろえて、一括代入できます。
destinationWorksheet.Range("A2:C1000").Value = _
sourceWorksheet.Range("A2:C1000").Value
行ごとに条件判定する必要がなければ、この方法の方が高速です。
・画面更新を一時停止する
大量データを処理する場合は、画面更新を停止すると処理時間を短縮できることがあります。
Application.ScreenUpdating = False
処理終了時には、必ず元へ戻します。
Application.ScreenUpdating = True
ただし、途中でエラーが発生すると、画面更新が停止したまま残る可能性があります。
実務で使用する場合は、エラーが起きても設定を元に戻せる構成にすることが重要です。
・自動計算を一時的に停止する
数式が多いブックでは、セルへ値を書き込むたびに再計算が発生し、処理が遅くなることがあります。
Application.Calculation = xlCalculationManual
処理後は次のように戻します。
Application.Calculation = xlCalculationAutomatic
計算方法を変更する場合も、エラー発生時を含めて必ず復元する設計が必要です。
✅ 高速化と安全性を両立した実務向けコード
大量データを処理する場合は、Excelの設定を一時変更し、処理後に必ず元へ戻す構成が適しています。
Option Explicit
Sub CopyRowsSafely()
Dim sourceWorksheet As Worksheet
Dim destinationWorksheet As Worksheet
Dim lastSourceRow As Long
Dim destinationRow As Long
Dim sourceRow As Long
Dim previousCalculation As XlCalculation
On Error GoTo ErrorHandler
Set sourceWorksheet = ThisWorkbook.Worksheets("元データ")
Set destinationWorksheet = ThisWorkbook.Worksheets("転記先")
' 現在の計算設定を保存してから変更する
previousCalculation = Application.Calculation
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
lastSourceRow = sourceWorksheet.Cells( _
sourceWorksheet.Rows.Count, "A" _
).End(xlUp).Row
destinationRow = destinationWorksheet.Cells( _
destinationWorksheet.Rows.Count, "A" _
).End(xlUp).Row + 1
For sourceRow = 2 To lastSourceRow
If Len(Trim$(CStr( _
sourceWorksheet.Cells(sourceRow, "A").Value _
))) > 0 Then
destinationWorksheet.Cells(destinationRow, "A") _
.Resize(1, 3).Value = _
sourceWorksheet.Cells(sourceRow, "A") _
.Resize(1, 3).Value
destinationRow = destinationRow + 1
End If
Next sourceRow
CleanExit:
' 成否にかかわらずExcelの設定を元へ戻す
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = previousCalculation
Application.CutCopyMode = False
Exit Sub
ErrorHandler:
MsgBox "コピー処理中にエラーが発生しました。" & vbCrLf & _
"エラー内容：" & Err.Description, _
vbExclamation
Resume CleanExit
End Sub
このコードでは、画面更新・イベント・自動計算の現在設定を考慮しながら処理しています。
特に計算方法については、単純に最後にxlCalculationAutomaticへ変更するのではなく、処理前の設定をpreviousCalculationへ保存し、その状態へ戻しています。
これにより、もともと手動計算で使用していたブックの設定を勝手に変更せずに済みます。
また、エラー処理ではCleanExitへ移動し、途中で問題が起きてもExcelの設定を復元します。
単に高速化の設定を追加するだけでなく、確実に元へ戻すところまで設計することが実務では重要です。
✅ 繰り返しコピーで起こりやすいエラーと注意点
コピー処理を繰り返す場合は、次のような問題が起こりやすくなります。
・コピー元と貼り付け先の列数が合っていない
値を直接代入する場合、コピー元と貼り付け先の範囲サイズが異なるとエラーになります。
destinationRange.Value = sourceRange.Value
この書き方を使う場合は、行数と列数が一致しているか確認しましょう。
・最終行の基準列が適切でない
A列を基準に最終行を取得していても、A列だけ途中で空白になっていると、実際より上の行を最終行として取得することがあります。
どの列に必ずデータが入るのかを確認し、最終行取得の基準列を選ぶ必要があります。
・転記先を毎回同じ行にしている
ループ内でdestinationRowを増やし忘れると、すべてのデータが同じ行へ上書きされます。
destinationRow = destinationRow + 1
この処理を、転記が成功したタイミングで実行することが重要です。
・結合セルが含まれている
コピー元または貼り付け先に結合セルがあると、範囲サイズが合わず、コピーできない場合があります。
業務マクロでは、データ一覧に結合セルを使わない設計の方が安全です。
・数式の参照先が変わる
Copy Destinationで数式をコピーすると、相対参照が貼り付け位置に応じて変化します。
数式をそのまま複製したいのか、計算結果だけを値として残したいのかを事前に決めましょう。
値貼り付けでエラーが発生する場合は、コピー範囲と貼り付け範囲の不一致や、結合セル、クリップボードの状態なども確認が必要です。具体的な原因と対処法は【VBA】値貼り付け：できない（エラー）原因と解決策｜PasteSpecial失敗を防ぐ実務対策で解説しています。
✅ まとめ：コピー内容とデータ量に応じて処理を選ぶ
VBAでコピーと貼り付けを繰り返す場合は、For文やFor Each文を使うことで、複数行・複数シートの転記を自動化できます。
ただし、すべてのケースでCopyメソッドを繰り返せばよいわけではありません。
実務では、次のように使い分けることが重要です。
- 値・数式・書式をまとめて複製する：
Copy Destination - 値だけを転記する：
Valueの直接代入 - 貼り付ける内容を細かく選ぶ：
PasteSpecial - 条件に合う行だけ転記する：
For文とIf文 - 複数シートから集める：
For Each文 - 既存データの続きへ追加する：貼り付け先の最終行を取得
- 大量データを処理する：一括代入や画面更新停止を検討
- 高速化設定を使う：エラー時にも必ず元へ戻す
コピー元・貼り付け先・繰り返し条件を明確に分けて設計すると、後から列やシートが変更された場合にも対応しやすくなります。
単に操作を自動化するだけでなく、上書きや転記漏れを防ぎ、繰り返し使える構成にすることが、実務で安全なコピー処理につながります。