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

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

Sub test()

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

End Sub

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

当ブログではVBAだけでなくPythonも解説しています。
PythonでもExcel操作ができるので、第２言語としておすすめです。
>>VBAの次にPythonを学ぶメリットと勉強方法

-index-

単体セルのコピー＆ペスト
複数セルのコピー＆ペースト
行(列)のコピー＆ペースト
貼り付け方の変更方法
- 数値貼り付け
- 行列を入れ替えて貼り付け
コピー範囲の自動取得
別のシートからデータをコピーする方法
別ファイルのデータをコピーする方法
別のファイルから該当する項目を探してコピーする方法
- Setでオブジェクト指定を変数化する
別ファイルから全ての項目をコピーする方法
まとめ

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

Copy

Sub test()

Cells(1, 1).Copy

End Sub

Cells(行, 列)でセルを指定してCopyでコピーします。

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

【関連記事】Excelマクロでセル,シート,ファイルを指定する方法

PasteSpecial

Sub test()

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

End Sub

貼り付けたいセルを指定して、PasteSpecialで貼り付けます。
つまり、Copy -> PasteSpecialでコピペできます。

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に貼り付けできます。
セル範囲を指定してCopyし、左上になる貼り付け先を指定してPasteSpecialです。

ただし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

行をコピペする場合、RowsとCopyでコピーし、貼り付け先を左端のセルにします。
列の場合は、ColumnsとCopyでコピーし、１行目となるセルに貼り付けましょう。

貼り付け方の変更方法

数値貼り付け

Sub test()

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

End Sub

PasteSpecial Paste :=
　・通常貼り付け：xlPasteAll
　・値で貼り付け：xlPasteValues
　・書式だけ貼り付け：xlPasteFormats

例えば以下のように、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

PasteSpecial Transpose := True or False

行列を入れ替えるには引数Transposeを設定します。
もちろんPasteの引数と一緒に使っても大丈夫です。

コピー範囲の自動取得

項目名となる列名を含まずに、最下行までコピーしたいときがたま～にありますよね。

もし、11行目までデータが入っていると決まっているなら、Range(“A2:A11”).CopyでOK。
しかし、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

これでendRowにデータが入っている最下行の行番号が入ります。
Excelシートの最も下にあるセルからCtrl+↑をすると、データが入っている最終行に移動します。

【関連記事】Excelマクロで最終行(列)を取得する方法

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

Cells(1, Columns.Count).End(xlToLeft).Column

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

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

コピー先：”Book1.xlsx”の１枚目のA1
貼付け先：プログラム実行ファイルの１枚目のA1

ファイル->シート->セルの順に指定して、CopyとPasteSpecialを使います。

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

・”Book1.xlsx”に”sampleB”の項目がある
・プログラム実行ファイルから、”sampleB”を見つけて”Book1.xlsx”に貼り付けたい
という状況だとします。

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”ですね。
endColは、プログラム実行ファイルでデータが入っている最終列です。

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

InStr(“文字列”, “検索文字”)は、検索文字が含まれないなら０、含まれるなら１以上です。
見つけたらコピーして”Book1.xlsx”に貼り付けるだけです。

【関連記事】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”~”sampleC”の項目がある
・プログラム実行ファイルから、すべての項目を”Book1.xlsx”に貼り付けたい
という状況です。

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文で繰り返すだけで解決します。

変数i：どの項目をコピーするのか決めるためのfor文
変数j：コピーしたい項目を探すためのfor文