サクッとコピペ ExcelVBA

サクッとコピペして、使えるVBAコード集

セルの値を張り付ける PasteSpecialメソッド

1. Excelの<形式を選択して貼り付け>をマクロで行う(PasteSpecialメソッド)

仕事の場面では、単純なセルのすべての貼り付けだけでなく、値の貼り付けだったり、関数の貼り付けだったり、文字のスタイルだったり、罫線だったりのみを張り付けたいようなケースが多々あります。その場合、Excelにおいては<形式を選択して貼り付け>を選んで張り付けること思います。VBAでRangeオブジェクトに属するPaseteメソッドは、すべてを張り付けてしまうので、様々な貼り付けに対応できません。代わりにPasteSpecialメソッドがあります。実際の処理を見てみましょう。

VBA実行前>

値を張り付ける VBA実行前

ここでは、日本語の売上報告を英語版のSales Reportに張り付けています。日本語の売上報告は、MS Pゴシックの書式で、英語版はTimes New Romanを書式として使用しています。

VBA実行後>

値の貼り付け VBA実行後

単純にすべてを張り付けてしまったり、書式を張り付けてしまうと英語のSales Reportと書式がずれてしまいます。この場合は値を張り付ける処理をするのか、セルオブジェクトに一つ一つを代入する方法も検討しなければならないですね。

<実際のコード>

Sub 貼り付け()

'「すべて」で貼り付け
   Worksheets("sheet1").Range("B3:D3").Copy
   Worksheets("sheet1").Range("G3:I3").PasteSpecial Paste:=xlPasteAll

'「値」で貼り付け
   Worksheets("sheet1").Range("B3:D3").Copy
   Worksheets("sheet1").Range("G6:I6").PasteSpecial Paste:=xlPasteValues

'「数式」、「書式」の貼り付け
   Worksheets("sheet1").Range("B3:D3").Copy
   Worksheets("sheet1").Range("G9:I9").PasteSpecial Paste:=xlPasteFormulas
   Worksheets("sheet1").Range("B3:D3").Copy
   Worksheets("sheet1").Range("G9:I9").PasteSpecial Paste:=xlPasteFormats
    
'セルオブジェクトの操作(格納)
    Cells(13, 7).Value = Cells(3, 2).Value
    Cells(13, 8).Value = Cells(3, 3).Value
    Cells(13, 9).Value = Cells(3, 4).Value

End Sub

2.メソッドの使用方法

端的に示します。Rangeオブジェクトに続けて、

  • .PasteSpecial Paste:= 引数(xlPasteValuesなど)
  • .PasteSpecial Transpose:=True (行と列を入れ替え)
  • .PasteSpecial Operation:= 引数(xlPasteSpecialOperationAddなど)

3.PasteSpecialメソッドのプロパティとExcelの<形式を選択して貼り付け>の対比表

プロパティ名 定数の説明 Excelの形式を選択して貼り付け
xlPasteAll すべて貼り付ける すべて
xlPasteFormulas 数式を張り付ける 数式
xlPasteValues 値で張り付ける
xlPasteFormats 書式を張り付ける 書式
xlPasteComments コメントとメモを張り付ける コメントとメモ
xlPasteValidation 入力規則を張り付ける 入力規則
xlPasteAllUsingSourceTheme ソースのテーマを使用してすべてを貼り付けます コピー元のテーマを使用してすべて貼り付け
xlPasteAllExceptBorders 罫線を除くすべてを張り付ける 罫線を除くすべて
xlPasteColumnWidths 列幅を張り付ける 列幅
xlPasteFormulasAndNumberFormats 数式と数値の書式を張り付ける 数式と数値の書式
xlPasteValuesAndNumberFormats 値と数値を張り付ける 値と数値の書式
xlPasteAllMergingConditionalFormats すべての結合されている条件付き書式を張り付ける すべての結合されている条件付き書式
xlPasteSpecialOperationNone 貼り付けで演算はしない なし
xlPasteSpecialOperationAdd コピーしたセルを張り付け対象セルに加算する 加算
xlPasteSpecialOperationSubtract コピーしたセルを張り付け対象セルに減算する 減算
xlPasteSpecialOperationMultiply コピーしたセルの値でを張り付け対象セルを乗算する 乗算
xlPasteSpecialOperationDivide コピーしたセルの値でを張り付け対象セルを除算する 除算
SkipBlanks クリップボードに含まれる空白のセルを貼り付けの対象にしないようにする。デフォルトはFalse、実行時Trueにする。 空白セルを無視する
Transpose 貼り付けのときにデータの行と列を入れ替える。デフォルトはFalse、実行時Trueにする 行と列の入れ替え

 

プライバシーポリシーはこちら