【マクロVBA】複数の繰り返しのコピー&ペーストが面倒くさい!?高速で一括で貼り付けできる配列とは

VBAを使用する中でコピー&ペーストの機能は使用したことがあると思います。
しかしコピー&ペーストは1つコピーして別のデータをコピーすると
1つ目のコピー情報は消えてしまいます。

そこで複数コピー&ペーストしたい場合は1つコピーして貼り付け…1つコピーして貼り付け…
という動作をコピー&ペーストしたい回数繰り返さないといけません。

さらにシートをまたぐ際には動作時間も遅くなりますし
プログラムも長文になってしまいます。

そこで今回は配列を使用して配列に情報を蓄積していき
貼り付け位置にまとめてペーストする方法について紹介したいとします。

それでは見ていきましょう。

■複数のコピー&ペーストの問題点
■配列に格納して貼り付けする
■まとめ

■複数のコピー&ペーストの問題点

まず解決策を紹介する前に比較対象として
下図の見本を用意しました。

シート名「Befor」の数値1～7をシート名「After」に貼り付けようとします。
ではコピー&ペーストを使用したプログラムを見てみましょう。

Sub BeforからAfterにコピーペースト()
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“B4:B8”).Copy ‘数値1をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“B4”).PasteSpecial xlPasteValues ‘数値1を値のみペースト
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“D11:D15”).Copy ‘数値2をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“C4”).PasteSpecial xlPasteValues ‘数値2を値のみペースト
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“G8:G12”).Copy ‘数値3をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“D4”).PasteSpecial xlPasteValues ‘数値3を値のみペースト
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“G17:G21”).Copy ‘数値4をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“E4”).PasteSpecial xlPasteValues ‘数値4を値のみペースト
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“I8:I12”).Copy ‘数値5をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“F4”).PasteSpecial xlPasteValues ‘数値5を値のみペースト
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“J17:J21”).Copy ‘数値6をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“G4”).PasteSpecial xlPasteValues ‘数値6を値のみペースト
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
Range(“L9:L13”).Copy ‘数値7をコピー
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“H4”).PasteSpecial xlPasteValues ‘数値7を値のみペースト
End Sub

長いですね。

実行結果は下図の通りです。

コピー元のシートとペースト先のシートが異なるため
シートを毎回アクティブにしないといけないという悪さがあります。

この長く見にくいプログラムを配列を使用することで簡潔にすることができます。

■配列に格納して貼り付けする

では本題の配列を使用して貼り付けを行いたいと思います。
配列なので変数は「Variant型」になります。

変数についての記事はこちらから☟

【VBA知識 6】変数とは?変数の一覧と意味・変数の宣言をする ► 独学エクセル塾 (dokugakuexcel.com)

今回は見本で「A」というVariant型の変数を使用したいと思います。
ではプログラムから見てみましょう。

Sub BeforからAfterに配列を使用してコピーペースト()
Dim A(7) As Variant
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
A(1) = Range(“B4:B8”) ‘数値1を格納
A(2) = Range(“D11:D15”) ‘数値2を格納
A(3) = Range(“G8:G12”) ‘数値3を格納
A(4) = Range(“G17:G21”) ‘数値4を格納
A(5) = Range(“I8:I12”) ‘数値5を格納
A(6) = Range(“J17:J21”) ‘数値6を格納
A(7) = Range(“L9:L13”) ‘数値7を格納
Worksheets(“After”).Activate ‘シートAfterをアクティブ
Range(“B4:B8”) = A(1) ‘数値1を貼り付け
Range(“C4:C8”) = A(2) ‘数値2を貼り付け
Range(“D4:D8”) = A(3) ‘数値3を貼り付け
Range(“E4:E8”) = A(4) ‘数値4を貼り付け
Range(“F4:F8”) = A(5) ‘数値5を貼り付け
Range(“G4:G8”) = A(6) ‘数値6を貼り付け
Range(“H4:H8”) = A(7) ‘数値7を貼り付け
End Sub

いかがでしょうか。
すっきりしましたね。

実行した結果も見てみましょう。

無事、データを貼り付けすることができました。
では仕組みを解説します。

Dim A(7) As Variant
こちらで配列のAという変数が（7）で7つあることを宣言します。

A(1) = Range(“B4:B8”) ‘数値1を格納
・・・
A(7) = Range(“L9:L13”) ‘数値7を格納
A(1)から順にA(7)まで数値1から7を格納していきます。

Range(“B4:B8”) = A(1) ‘数値1を貼り付け
・・・
Range(“H4:H8”) = A(7) ‘数値7を貼り付け
ここで指定した位置にA(1)からA(7)まで順番に出力を行います。

コピー&ペーストを繰り返していた作業が楽になりましたね。
さらにFor Nextを使用することも可能です。

For Nextについてはこちらから☟

【VBA知識 7】FOR…NEXTで指定した数値で繰り返し・ループ動作をする。 ► 独学エクセル塾 (dokugakuexcel.com)

Sub BeforからAfterに配列を使用してコピー&ペースト()
Dim A(7) As Variant, B As Long
Worksheets(“Befor”).Activate ‘シートBeforをアクティブ
A(1) = Range(“B4:B8”) ‘数値1を格納
A(2) = Range(“D11:D15”) ‘数値2を格納
A(3) = Range(“G8:G12”) ‘数値3を格納
A(4) = Range(“G17:G21”) ‘数値4を格納
A(5) = Range(“I8:I12”) ‘数値5を格納
A(6) = Range(“J17:J21”) ‘数値6を格納
A(7) = Range(“L9:L13”) ‘数値7を格納
Worksheets(“After”).Activate ‘シートAfterをアクティブ
For B = 1 To 7 Step 1
Range(Cells(4, B + 1), Cells(8, B + 1)) = A(B) ‘数値1を貼り付け
Next B
End Sub