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

VBA
スポンサーリンク

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

A1セルをコピーするコード

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

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

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

Copy

Sub test()

Cells(1, 1).Copy

End Sub
A1セルをコピーした図
Cells(行, 列)でセルを指定してCopyでコピーします。
A1セルをコピーするコード

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

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

PasteSpecial

Sub test()

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

End Sub
貼り付けたいセルを指定して、PasteSpecialで貼り付けます。
B1セルにペーストした図

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
Range(“左上セル:右下セル”).Copy

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

複数範囲のセルをコピーした図

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

Sub test()

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

End Sub
Range(Cells(), Cells())

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

RangeとCellsで範囲指定するコード

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

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

Sub test()

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

End Sub

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

行をコピペした図

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

貼り付け方の変更方法

数値貼り付け

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列の合計を入れています。

C列をコピーする図

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

Sub test()

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

End Sub
C列をデフォルト条件でコピペした図

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

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

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

Sub test()

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

End Sub
C列を値で貼付けとしてコピペした図

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

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

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の引数と一緒に使っても大丈夫です。

コピー範囲の自動取得

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

A2以下のセルをコピーする図

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

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

A2以下のセルをコピーする図

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

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
最下行のセルを取得するコード
①Rows.Countは最も下にある行(何万行目とか)
②End(xlUp)はCtrl + ↑を実行するコード

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

最下行のセルを指定した図

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

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

詳しくは、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
コピー先 :”Book1.xlsx”の1枚目のA1
ペースト先:プログラム実行ファイルの1枚目のA1

このように、ファイル,シート,セルの順に指定して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文で1列目から最終列まで処理をかけて、”sampleB”の列を探しましょう。

別ファイルからデータをコピペするコード

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

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

InStr(“文字列”, “検索文字”) > 0:検索文字が含まれる

これについては、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文
変数j:コピーしたい項目を探すためのfor文

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

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

別ファイルから全ての列をコピペした図

まとめ

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

①コピペしたい項目を取得する
②コピペしたいデータの位置を取得する
③Copyでコピーし、PasteSpecialで貼り付け

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

コメント

タイトルとURLをコピーしました