VBA上でコピーや切り取りしてクリップボードに格納された情報を形式を選択して貼り付けするにはPasteSpecialメソッドを使います。
所定の罫線などの書式を崩さずに貼り付けしたい場合などは「形式を選択して貼り付け」の機能は非常に便利なので、日常的に使う機能のひとつですね。
PasteSpecialメソッドの基本構文
Rangeオブジェクト.PasteSpecial(Paste,Operation,SkipBlanks,Transpose)
Rangeオブジェクト
Pasteメソッドを指定するRangeオブジェクトを指定します。
オブジェクトに関しては下記記事をご参照ください。
Excelのオブジェクトの基礎知識まとめ | Excelを制する者は人生を制す ～No Excel No Life～
引数Paste
クリップボードの情報を貼り付ける形式を指定できる引数です。
この引数は省略可能です。
省略した場合はクリップボードの情報をすべて貼り付けます。（Pasteメソッドと同じ）
引数Operation
クリップボードの情報で貼り付けする際に四則演算も合わせて行なうか指定できる引数です。
この引数は既定値が「演算をしない」となっており、省略可能です。
引数SkipBlanks
クリップボードの情報で貼り付けする際に「空白セルを無視する」かどうかを指定できる引数です。
この引数は既定値が「無視しない」となっており、省略可能です。
引数Transpose
クリップボードの情報で貼り付けする際に「行列を入れ替える」かどうかを指定できる引数です。
この引数は既定値が「入れ替えない」となっており、省略可能です。
使用例
今回は下記のようにB3:B7の範囲にあるセル情報をD3:D7の範囲に貼り付けるとします。
引数Pasteの代表的な使用方法
引数Pasteは、ワークシート上で「形式を選択して貼り付け」を行なう際に表示される「形式を選択して貼り付け」ダイアログの中の「貼り付け」メニューにある機能をすべて指定することができます。
この中からいくつか代表的な使い方をご紹介していきます。
【参考】値のみ貼り付けの場合
値のみ貼り付けの場合は以下のコードになります。
|
1 2 3 4 |
Sub 形式を選択して貼り付け①() Range("B3:B7").Copy Range("D3").PasteSpecial Paste:=xlPasteValues End Sub |
最初にコピー元となるB3:B7のセル範囲をCopyメソッドにてコピー。（2行目）
貼り付け先の起点となるD3セルを指定し、そのあとにPasteSpecial メソッドを記述。
そのあとに引数「Paste」として"Paste:="のあとに「値のみ」を意味する定数「xlPasteValues」を記述で完了です。（3行目）
B3:B7の情報がD3:D7へ値のみ貼り付けすることができました！
【参考】書式のみ貼り付けの場合
書式のみ貼り付けの場合は以下のコードになります。
|
1 2 3 4 |
Sub 形式を選択して貼り付け②() Range("B3:B7").Copy Range("D3").PasteSpecial Paste:=xlPasteFormats End Sub |
値のみと異なるのは引数「Paste」の定数が「書式のみ」を意味する「xlPasteFormats」になっている点のみです。
B3:B7の情報がD3:D7へ書式のみ貼り付けすることができました！
【参考】列幅のみ貼り付けの場合
若干マニアックですが、列幅のみ貼り付けの場合は以下のコードになります。
|
1 2 3 4 |
Sub 形式を選択して貼り付け③() Range("B3:B7").Copy Range("D3").PasteSpecial Paste:=xlPasteColumnWidths End Sub |
値のみと異なるのは引数「Paste」の定数が「列幅のみ」を意味する「xlPasteColumnWidths」になっている点のみです。
B3:B7の情報がD3:D7（というよりはD列）へ列幅のみ貼り付けすることができました！
引数Pasteの定数一覧
上記で紹介したものも含めて引数Pasteの定数を以下の通りすべて一覧にしましたのでご参考になさってください。
| すべて | xlPasteAll（既定） |
|---|---|
| 数式 | xlPasteFormulas |
| 値 | xlPasteValues |
| 書式 | xlPasteFormats |
| コメント | xlPasteComments |
| 入力規則 | xlPasteValidation |
| 罫線を除く全て | xlPasteAllExceptBorders |
| 列幅 | xlPasteColumnWidths |
| 数式と数値の書式 | xlPasteFormulasAndNumberFormats |
| 値と数値の書式 | xlPasteValuesAndNumberFormats |
| コピー元のテーマを使用してすべて貼り付け | xlPasteAllUsingSourceTheme |
| すべての結合されている条件付き書式 | xlPasteAllMergingConditionalFormats |
【参考】引数Operationを指定する場合
引数Operationは、ワークシート上で「形式を選択して貼り付け」を行なう際に表示される「形式を選択して貼り付け」ダイアログの中の「演算」メニューにある機能をすべて指定することができます。
たとえば、D3:D7の範囲にそれぞれ「2」という数値が入っていたとします。
ここにB3:B7の範囲の情報をD3:D7にもともと入っている値へ加算するとします。（値のみ貼り付け）
その場合の以下のコードになります。
|
1 2 3 4 |
Sub 形式を選択して貼り付け④() Range("B3:B7").Copy Range("D3").PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd End Sub |
引数「Paste」の定数部分までは先述の通りです。
そのあとに,[カンマ]、そして引数「Operation」として"Operation:="のあとに「加算」を意味する定数「xlPasteSpecialOperationAdd」を記述で完了です。（3行目）
B3:B7の情報がD3:D7へ加算した上で値のみ貼り付けすることができました！
引数Operationの定数一覧
上記で紹介したものも含めて引数Operationの定数を以下の通りすべて一覧にしましたのでご参考になさってください。
| しない | xlPasteSpecialOperationNone（既定） |
|---|---|
| 加算 | xlPasteSpecialOperationAdd |
| 減算 | xlPasteSpecialOperationSubtract |
| 乗算 | xlPasteSpecialOperationMultiply |
| 除算 | xlPasteSpecialOperationDivide |
【参考】引数SkipBlanksを指定する場合
引数SkipBlanksは、ワークシート上で「形式を選択して貼り付け」を行なう際に表示される「形式を選択して貼り付け」ダイアログの中の「空白セルを無視する」のチェック有無を指定することができます。
たとえば、B4:B6が罫線のみでブランクだったとします。
B3:B7の範囲のブランクセルを除いたセルの書式だけをD3:D7へ貼り付けしたいとします。
その場合の以下のコードになります。
|
1 2 3 4 |
Sub 形式を選択して貼り付け⑤() Range("B3:B7").Copy Range("D3").PasteSpecial Paste:=xlPasteFormats, SkipBlanks:=True End Sub |
引数「Paste」の定数部分までは先述の通りです。
そのあとに,[カンマ]、そして引数「SkipBlanks」として"SkipBlanks:="のあとに「空白セルを無視する」を意味する定数「True」を記述で完了です。（3行目）
D3セルとD7セルのみ書式を貼り付けすることができました！
【参考】引数Transposeを指定する場合
引数Transposeは、ワークシート上で「形式を選択して貼り付け」を行なう際に表示される「形式を選択して貼り付け」ダイアログの中の「行列を入れ替える」のチェック有無を指定することができます。
行列を入れ替える場合は以下のコードになります。（値のみ貼り付け）
|
1 2 3 4 |
Sub 形式を選択して貼り付け⑥() Range("B3:B7").Copy Range("D3").PasteSpecial Paste:=xlPasteValues, Transpose:=True End Sub |
引数「Paste」の定数部分までは先述の通りです。
そのあとに,[カンマ]、そして引数「Transpose」として"Transpose:="のあとに「行列を入れ替える」を意味する定数「True」を記述で完了です。（3行目）
B3:B7の情報がD3:H3へ値のみ貼り付けすることができました！
まとめ
「形式を選択して貼り付け」はほんとうに応用できる場面が多いため、上記を参考にいろいろとコードを組んでみてみましょう。
なお、そんな便利なPasteSpecialメソッドですが、「形式を選択して貼り付け」ダイアログの左下にある「リンク貼り付け」だけはできませんのでご注意を。
もし、「リンク貼り付け」したい場合はPasteメソッドの引数Linkにて指定することができますので混同しないようにしましょう(・∀・)
【Excel VBA】クリップボードの情報を貼り付けるPasteメソッド | Excelを制する者は人生を制す ～No Excel No Life～
ご参考になれば幸いです(*^^*)
