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

VBA
スポンサーリンク

VBAのコピペはめちゃくちゃ単純です。
CopyとPasteSpecialの2つだけ知っておけば、誰でもできます。

私はfor文すら知らないプログラミングのド素人からVBAとPythonを独学しました。

会社では通常業務に加えて作業自動化と機械学習をしています。

コピペを自動化したい
・自動で列を検索してコピーしてほしい
・一連の事務作業をすべて自動化したい

こんな人におススメの内容です。

VBAを使えば、コピペ程度の事務作業は数秒で完了します。

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

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

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列をコピーする図

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

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

コピー範囲の自動取得

下図のようにA列の2行目以下をコピーしたいとします。

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

項目名はコピーしたくないって時ありますよね。

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

しかしA列のデータが何行目まで入っているかわからないときは設定できません。

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

つまり、A列の2~X行目までコピーする方法を紹介します

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 + ↑を実行するプログラムです。

結果的に、データが入っている最終行にたどり着きます。

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

変数endRowには最終行の行番号が入るのでコピー範囲を自動設定できています。

もし最終列を取得したいなら以下のように書きましょう。

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”)です。

endColは、先ほど紹介した方法で取得できる最終列のこと。

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で貼り付け

これでコピペが自動化できるようになれば嬉しいですね。

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

VBA
スポンサーリンク
HTOMblog

コメント

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