Categories: VBA基礎

EXCEL VBA Copy PasteSpecial xlPasteSpecialOperation（セルのコピー・貼り付け）

EXCEL VBA Copy PasteSpecial　xlPasteSpecialOperation（セルのコピー・貼り付け）

●はじめに

ここでは、セルの値やセルの書式・計算式などを他のセルへ貼り付ける。「Copy　PasteSpecial(Paste, Operation, SkipBlanks, Transpose」の使い方を説明いたします。セルからセルへ張り付ける際、加算して貼り付けなど、さまざまな方法でセルのデータ等を張り付ける事ができます。また、表のデータを一つに取りまとめたりすることも出来ますので、この機能を上手く利用することで業務効率も図る事が出来ます。

●書式の説明

Rangeオブジェクト.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Paste引数（貼り付ける書式等を指定する）

●書式の説明

Rangeオブジェクト.PasteSpecial(Paste 定数）

定数	内容	使用例
xlPasteAll	全て（規定値）	Range("A1").PasteSpecial xlPasteAll
xlPasteFormulas	数式	Range("A1").PasteSpecial xlPasteFormulas
xlPasteValues	値	Range("A1").PasteSpecial xlPasteValues
xlPasteFormats	書式	Range("A1").PasteSpecial xlPasteFormats
xlPasteComments	コメント	Range("A1").PasteSpecial xlPasteComments
xlPasteValidation	入力規則	Range("A1").PasteSpecial xlPasteComments
xlPasteAllExceptBorders	罫線を除く全て	Range("A1").PasteSpecial xlPasteAllExceptBorders
xlPasteColumnWidths	列幅	Range("A1").PasteSpecial xlPasteColumnWidths
xlPasteFormulasAndNumberFormats	数式と数値の書式	Range("A1").PasteSpecial xlPasteFormulasAndNumberFormats
xlPasteValuesAndNumberFormats	値と数式の書式	Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
xlPasteAllUsingSourceTheme	コピー元のテーマを使用してすべて貼り付け	Range("A1").PasteSpecial xlPasteAllUsingSourceTheme
xlPasteAllMergingConditionalFormats	すべての結合されている条件付き書式	Range("A1").PasteSpecial xlPasteAllMergingConditionalFormats

●プログラム説明

下記にコピー・貼り付け方法のサンプルプログラムを作成しましたので、参照して下さい。貼り付け方法は、複数ありますが、ここでは、５つに絞り普段利用するものに絞りました。

Sub Paste()

    Range("C1:C9").Clear 'C1~C9を全てクリアする

    Range("A1").Copy
    Range("C1").PasteSpecial xlPasteAll '全て
    
    Range("A3").Copy
    Range("C3").PasteSpecial xlPasteFormulas '数式
    
    Range("A5").Copy
    Range("C5").PasteSpecial xlPasteValues '値
    
    Range("A7").Copy
    Range("C7").PasteSpecial xlPasteFormats '書式
    
    Range("A9").Copy
    Range("C9").PasteSpecial xlPasteComments 'コメント

End Sub

●実行結果

実行後、A列の値をC列に張り付けた結果です。

●実行前　※元号と年月日が表示されていません。（クリックして拡大）

●実行後　※元号と年月日が表示されていません。（クリックして拡大）

Operation引数(貼り付ける際に四則演算を行う)

●書式の説明

Rangeオブジェクト.PasteSpecial Paste 定数,（Operation定数）

定数	内容	使用例
xlPasteSpecialOperationNone	演算しない	Range("A1").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone
xlPasteSpecialOperationAdd	加算	Range("A1").PasteSpecial xlPasteAll, xlPasteSpecialOperationAdd
xlPasteSpecialOperationSubtract	減算	Range("A1").PasteSpecial xlPasteAll, xlPasteSpecialOperationSubtract
xlPasteSpecialOperationMultiply	乗算	Range("A1").PasteSpecial xlPasteAll, xlPasteSpecialOperationMultiply
xlPasteSpecialOperationDivide	除算	Range("A1").PasteSpecial xlPasteAll, xlPasteSpecialOperationDivide

●プログラム説明（サンプルプログラム①）

下記にOperationの使い方を説明します。コピー・貼り付けを行う際に、A列とC列で四則演算を行いデータを張り付ける事が出来ます。サンプルを作成しましたので、参照して下さい。

Sub PasteSpecial()

    Range("A1").Copy
    Range("C1").PasteSpecial xlPasteAll, xlPasteSpecialOperationAdd '加算
    
    Range("A3").Copy
    Range("C3").PasteSpecial xlPasteAll, xlPasteSpecialOperationSubtract '減算
    
    Range("A5").Copy
    Range("C5").PasteSpecial xlPasteAll, xlPasteSpecialOperationMultiply '乗算
    
    Range("A7").Copy
    Range("C7").PasteSpecial xlPasteAll, xlPasteSpecialOperationDivide '除算
    
    Range("A9").Copy
    Range("C9").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone '演算しない

End Sub

●実行結果

A列の値をC列に張り付ける際に四則演算を行ってC列に張り付けた結果です。

●実行前　※元号と年月日が表示されていません。（クリックして拡大）

●実行後　※元号と年月日が表示されていません。（クリックして拡大）

●プログラム説明(サンプルプログラム②)

次のサンプルプログラムは、複数の表データを一つにまとめるプログラムです。Operationを使う事により、簡単に表データを加算する事ができます。
（東京支店・大阪支店・名古屋支店・福岡支店のデータを全店舗合計に加算しながら張り付けます）

Sub PastSpecial_Add()

    Range("B3:B13").ClearContents 'B3~B13の値をクリアする
    
    Range("E3:E13").Copy  '東京支店
    Range("B3:B13").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

    Range("H3:H13").Copy  '大阪支店
    Range("B3:B13").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
  
    Range("E18:E28").Copy  '名古屋支店
    Range("B3:B13").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

    Range("H18:H28").Copy  '福岡支店
    Range("B3:B13").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

End Sub

●実行結果

各支店データを全店舗合計に張り付けながら集計されました。

●実行前　※元号と年月日が表示されていません。（クリックして拡大）

●実行後　※元号と年月日が表示されていません。（クリックして拡大）

Skip Blanks引数(貼り付ける際に空白の指定)

●書式の説明

Rangeオブジェクト.PasteSpecial Paste 定数,Operation定数,（SkipBlanks定数）

空白は貼り付けしない（既定値）	空白も貼り付ける
SkipBlanks:=Ture	SkipBlanks:=False

Sub SkipBlanks()

    Range("E1:G10").Clear 'C1~E10を全てクリアする
    Range("C1:C10").Copy

    Range("E1").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, SkipBlanks:=Ture '空白は貼り付けしない。
    Range("G1").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, SkipBlanks:=False '空白も貼り付けする。

End Sub

Transpose引数（貼り付ける際の行列を入れ替える）

●書式の説明

Rangeオブジェクト.PasteSpecial Paste定数, Operation定数, SkipBlanks定数, （Transpose定数)

行列を入れ替える	行列を入れ替えない
Transpose:=True	Transpose:=False

●プログラム説明

次のサンプルプログラムは、表をコピーして貼り付けした際に、行列を入れ替えた場合と入れ替えない場合を行った場合のプログラムです。

Sub Transpose()

     Range("A201:M55").Clear 'A201~M55を全てクリアする

     Range("A2:M13").Copy
     Range("A20").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, SkipBlanks:=ture, Transpose:=True '行列入れ替える
     Range("A40").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, SkipBlanks:=ture, Transpose:=False '行列入れ替えない

End Sub

●実行結果

行列を入れ替えた場合は、横軸に「勘定科目」・縦軸に月が入りました。表の行列の入れ替えは、一からプログラムを考えたり、処理スピードを考えると非常に効率的な機能なので、状況に応じて活用してください。

●実行前　※コピー元の表（クリックして拡大）

●実行後　※行列を入れ替えたて張り付けた場合（クリックして拡大）

●実行後　※行列を入れ替えない場合（そのまま貼り付ける）（クリックして拡大）

最後まで、ご覧いただきまして誠に有難うございました。Copy　PasteSpecialは、貼り付け先に、計算して張り付けたり、行列を変更して貼り付ける事ができますので、上手く利用すると便利な機能です。サンプルプログラム等を参照して、作成する方法を覚えて下さい。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。