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

VBA
スポンサーリンク

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

Sub test()

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

End Sub

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

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

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

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
A1セルをB1にコピーした図

貼り付けたいセルを指定して、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にコピーした図

このコードで、A1~C3までの9個のセルをE1~G3に貼り付けできます。
セル範囲を指定してCopyし、左上になる貼り付け先を指定してPasteSpecialです。

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

Sub test()

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

End Sub
RangeとCellsでコピー範囲を決めるコード

それぞれCellsで左上のセルと右下のセルを指定しましょう。
この方法なら、行列の番号をいちいちアルファベットに変換しなくていいので楽です。

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

Sub test()

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

End Sub
行をコピーした図

行をコピペする場合、RowsとCopyでコピーし、貼り付け先を左端のセルにします。
列の場合は、ColumnsとCopyでコピーし、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列の合計を入れているとします。

合計の関数列をコピーする図

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

Sub test()

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

End Sub
合計の関数をコピーすると0になった図

PasteSpecialでコピーすると0になりました。
これは数式ごとコピーしているので、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の引数と一緒に使っても大丈夫です。

コピー範囲の自動取得

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

A2セルより下をコピーしようとする図

もし、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
CountとEndで最下行を取得するコード

これで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”の1枚目のA1
貼付け先:プログラム実行ファイルの1枚目のA1
ブック、シート、セルを指定してコピーするコード

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

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

SampleBのデータを別ファイルにコピーしたい図
・”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文で1列目から最終列まで処理をかけて、”sampleB”の列を探しています。

SampleBをコピーするコードの解説図

InStr(“文字列”, “検索文字”)は、検索文字が含まれないなら0、含まれるなら1以上です。
見つけたらコピーして”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でどのシートなのかを設定します。
そうすると、定義した変数を使ってシートを指定できます。

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

SampleABCを別ファイルにコピーしたい図
・”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文
SampleABCを別ファイルにコピーした後の図

まとめ

今回はコピペの方法について解説しました。
重要なのは、コピペしたいセルや貼付け先の位置をどのように取得するかですね。

他にもファイルを開く方法と閉じる方法を解説しているので、ぜひ組み合わせてみてください。

VBAの次に新しい言語に挑戦したいな。。。

と考えている方にはPythonがおすすめです。勉強方法も解説しています。
>>VBAの次にPythonを学ぶメリットと勉強方法


コメント

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