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 |
●プログラム説明
下記にコピー・貼り付け方法のサンプルプログラムを作成しましたので、参照して下さい。貼り付け方法は、複数ありますが、ここでは、５つに絞り普段利用するものに絞りました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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列で四則演算を行いデータを張り付ける事が出来ます。サンプルを作成しましたので、参照して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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を使う事により、簡単に表データを加算する事ができます。
（東京支店・大阪支店・名古屋支店・福岡支店のデータを全店舗合計に加算しながら張り付けます）
（東京支店・大阪支店・名古屋支店・福岡支店のデータを全店舗合計に加算しながら張り付けます）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
|
1 2 3 4 5 6 7 8 9 |
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 |
●プログラム説明
次のサンプルプログラムは、表をコピーして貼り付けした際に、行列を入れ替えた場合と入れ替えない場合を行った場合のプログラムです。
|
1 2 3 4 5 6 7 8 9 |
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に関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。
