【VBA】Excelマクロでコピー＆ペーストを自動化する方法

コピーしたいセルや列などを指定して、Copyでコピーします。

次に貼り付けたいセルを指定して、PasteSpecialで終わりです。

ファイルを開く方法と閉じる方法も知っておけば、事務作業をすべて自動化することも可能です。

単体セルのコピー＆ペスト

Copy

Sub test()

Cells(1, 1).Copy

End Sub

VBAではオブジェクト（セルなど）を指定してから操作のコードを書きます。

セルの指定方法についてはExcelマクロでセル,シート,ファイルを指定する方法をご覧ください。

PasteSpecial

Sub test()

Cells(1, 1).Copy
Cells(1, 2).PasteSpecial

End Sub

Application.CutCopyMode

Sub test()

Cells(1, 1).Copy
Cells(1, 2).PasteSpecial
Application.CutCopyMode = False

End Sub

コピペをした後にコピー状態を示す破線が残ります。

これを解除したい場合はApplication.CutCopyModeをFalseにしましょう。

破線を残しておくと思わぬタイミングで貼り付けが行われるので、一応解除するといいですよ。

複数セルのコピー＆ペースト

Sub test()

Range("A1:C3").Copy
Cells(1, 5).PasteSpecial
Application.CutCopyMode = False

End Sub

A1～C3までの９個のセルをE1～G3に貼り付けできます。

ただしRange(“セル番地”)の書き方は引数を使いにくいので、以下の方法がおススメです。

Sub test()

Range(Cells(1, 1), Cells(3, 3)).Copy
Cells(1, 5).PasteSpecial
Application.CutCopyMode = False

End Sub

それぞれCellsで左上のセルと右下のセルを指定しましょう。

この方法なら行列番号をいちいちアルファベットに変換しなくて済みます。

行(列)のコピー＆ペースト

Sub test()

Rows(1).Copy
Cells(2, 1).PasteSpecial
Application.CutCopyMode = False

End Sub

行をコピペする場合、貼り付け先を左端のセルにします。

列をコピペするなら、１行目のセルに貼り付けましょう。

貼り付け方の変更方法

数値貼り付け

Sub test()

Cells(1, 1).copy
Cells(2, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

例えば以下のようにデータが入っているとします。

C列には関数SUMでA列とB列の合計を入れています。

PasteSpecialだけで、C列をG列にコピペしましょう。

Sub test()

Range("C2:C11").copy
Cells(2, 7).PasteSpecial
Application.CutCopyMode = False

End Sub

PasteSpecialでコピーすると０になりました。

これは数式ごとコピーしているので、G＝E＋Fになるからです。

そこで、Paste := xlPasteValues（数値貼り付け）に設定してみましょう。

Sub test()

Range("C2:C11").copy
Cells(2, 7).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

数値としてコピーされていますね。

行列を入れ替えて貼り付け

Sub test()

Range(Cells(1, 1), Cells(3, 3)).Copy
Cells(1, 5).PasteSpecial Transpose:=True
Application.CutCopyMode = False

End Sub

行列を入れ替えるには引数Transposeを設定します。

もちろんPasteの引数と一緒に使っても大丈夫です。

コピー範囲の自動取得

列名を含まずに、最下行までコピーしたいとします。

11行目までデータが入っていると確定しているなら、Range(“A2:A11”).Copyで大丈夫です。

しかし、A列が何行目まで入っているかわからないときは設定できませんよね。

この場合、最下行の行番号を取得する必要があります。

Sub test()

endRow = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(endRow, 1)).copy
Cells(2, 4).PasteSpecial
Application.CutCopyMode = False

End Sub

これでデータが入っている最下行にたどり着きます。

もし最終列を取得したいなら以下のとおりです。

詳しくは、Excelマクロで最終行(列)を取得する方法で解説しています。

別のシートからデータをコピーする方法

Sub test()

Worksheets(1).Cells(1, 1).Copy
Worksheets(2).Cells(1, 1).PasteSpecial
Application.CutCopyMode = False

End Sub

Worksheets().Cells()で、どのシートのどのセルかを指定できます。

指定できたらCopyとPasteSpecialでコピペすればOK。

別ファイルのデータをコピーする方法

Sub test()

Workbooks("Book1.xlsx").Worksheets(1).Cells(1, 1).Copy
ThisWorkbook.Worksheets(1).Cells(1, 1).PasteSpecial
Application.CutCopyMode = False

End Sub

このように、ファイル,シート,セルの順に指定してCopyまたはPasteSpecialを使います。

シートやファイル指定方法については、以下の記事で解説しています。

別のファイルから該当する項目を探してコピーする方法

“Book1.xlsx”のA1セルに、貼り付けたい項目名”sampleB”があるとします。

プログラム実行ファイルから”sampleB”の列を見つけてコピーしましょう。

Sub test()

Target = Workbooks("Book1.xlsx").Worksheets(1).Cells(1, 1)
endCol = ThisWorkbook.Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To endCol
    If InStr(ThisWorkbook.Worksheets(1).Cells(1, i), Target) > 0 Then
        ThisWorkbook.Worksheets(1).Columns(i).Copy
    End If
Next i

Workbooks("Book1.xlsx").Worksheets(1).Cells(1, 1).PasteSpecial
Application.CutCopyMode = False

End Sub

変数Targetがコピーしたい項目名(今回は”sampleB”)です。

for文で１列目から最終列まで処理をかけて、”sampleB”の列を探しましょう。

InStr(“文字列”, “検索文字”)は、文字列の左から何番目に検索文字があるかを求める関数です。

もし含まれないなら０になり、含まれるなら１以上の値になります。

これについては、Excelマクロで特定の文字を含むか判別する方法で詳しく解説しています。

Setでオブジェクト指定を変数化する

Sub test()

Dim copyWs As Worksheet
Dim pasteWs As Worksheet
Set copyWs = ThisWorkbook.Worksheets(1)
Set pasteWs = Workbooks("Book1.xlsx").Worksheets(1)

Target = pasteWs.Cells(1, 1)
endCol = copyWs.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To endCol
    If InStr(copyWs.Cells(1, i), Target) > 0 Then
        copyWs.Columns(i).Copy
    End If
Next i

pasteWs.Cells(1, 1).PasteSpecial
Application.CutCopyMode = False

End Sub

プログラムが大きくなると、同じシートを指定するコードを何回も書くことになります。

めんどくさいのでSetを使って変数にしましょう。

Dimで変数をWorksheetであることを定義し、Setでどのシートなのか設定します。

以降は定義した変数を使ってシートを指定できるので簡単です。

別ファイルから全ての項目をコピーする方法

“Book1.xlsx”ファイルに”sampleA～C”の項目が並んでいます。

プログラム実行ファイルから全ての項目をコピペしましょう。

Sub test()

Dim copyWs As Worksheet
Dim pasteWs As Worksheet
Set copyWs = ThisWorkbook.Worksheets(1)
Set pasteWs = Workbooks("Book1.xlsx").Worksheets(1)

copyEndCol = copyWs.Cells(1, Columns.Count).End(xlToLeft).Column
pasteEndCol = pasteWs.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To pasteEndCol
    Target = pasteWs.Cells(1, i)
    For j = 1 To copyEndCol
        If InStr(copyWs.Cells(1, j), Target) > 0 Then
            copyWs.Columns(j).Copy
        End If
    Next j
    pasteWs.Cells(1, i).PasteSpecial
    Application.CutCopyMode = False
Next i

End Sub

変数iのfor文は”Book1.xlsx”の各列を対象としています。

一方、変数jのfor文は実行ファイルの各列が対象ですね。

つまり、さっきのプログラムを変数iのfor文で繰り返しているだけです。

これを実行すると各項目がコピペされます。

まとめ

今回はコピペの方法について解説しました。

あとはファイルを開くプログラムと閉じるプログラムが使えれば、ほとんど手間なく事務作業を終わらせられます。