PR

【Excel VBA】よく使うPasteSpecialメソッド(形式を選択して貼付け)の使用例

その他
2024.02.23

Excel VBAで形式を選択して貼付けるときに使用する「PasteSpecial」メソッドの書き方についてご紹介します。

スポンサーリンク

PasteSpecialメソッドとは

通常のコピー＆ペーストで貼り付けを行うときは、下のように記述します。
Range(“A1”).Copy
Range(“B1”).Paste

この場合のPasteでは値（文字列や数字）、書式等すべての情報が貼り付けされます。

値だけ貼り付けたい場合や、書式だけ貼り付けたい場合などは「形式を選択して貼付け」という機能を使用して、指定した項目だけを貼り付けることが可能です。

手動で形式を選択して貼付けたいときは右クリックして表示される↓のウィンドウから選択します。

形式を選択して貼付けのウィンドウ

これをマクロで実行する方法について説明していきます。

スポンサーリンク

サンプルマクロのコピー前の状態

↓のエクセルシートのB2セルには数式が入っていてD2セル（10）+E2セル（20）の計算結果「30」が表示されています。このB2セルをコピーし、B4セルへ様々な形式で貼り付け処理を行ってみます。

ExcelVBA-PasteSpecialメソッド-コピー前の状態
スポンサーリンク

PasteSpecial（すべて貼り付け）

すべて貼り付けは「xlPasteAll」を使用します。
その名の通り、コピー元のセルに設定してある書式・数式含めすべてを貼り付けます。

.PasteSpecial Paste:=xlPasteAll

Sub PasteSpecialTest()

　Worksheets("Sheet1").Range("B2").Copy

　'すべて貼り付け
　Worksheets("Sheet1").Range("B4").PasteSpecial Paste:=xlPasteAll

　'コピーモード解除
　Application.CutCopyMode = False

End Sub

▽実行後
B2セルをコピーしてB4セルへ「xlPasteAll」ですべて貼り付けを行いました。
B2セルに設定している数式と書式が貼りついていることがわかります。
計算結果はD4セルの「30」とE4セルの「40」を足した「70」が表示されています。

ExcelVBA-PasteSpecialメソッド-すべて貼り付けで貼り付けた後の状態
スポンサーリンク

PasteSpecial（値のみ貼り付け）

値のみ貼り付けたいときは「xlPasteValues」を使用します。
書式・数式などは貼り付けず、値のみが貼り付けられます。

.PasteSpecial Paste:=xlPasteValues

Sub PasteSpecialTest()

　Worksheets("Sheet1").Range("B2").Copy

　'値のみ貼り付け
　Worksheets("Sheet1").Range("B4").PasteSpecial Paste:=xlPasteValues

　'コピーモード解除
　Application.CutCopyMode = False

End Sub

▽実行後
B2セルをコピーしてB4セルへ「xlPasteValues」で値のみ貼り付けを行いました。
B2セルの計算結果の値の「30」がB4セルに貼りつけられています。

ExcelVBA-PasteSpecialメソッド-値のみ貼り付けで貼り付けた後の状態
スポンサーリンク

PasteSpecial（書式のみ貼り付け）

書式のみ貼り付けは「xlPasteFormats」を使用します。
値や数式は貼りつきません。塗りつぶしの色やフォントの設定など書式設定のみが貼りつきます。

.PasteSpecial Paste:=xlPasteFormats

Sub PasteSpecialTest()

　Worksheets("Sheet1").Range("B2").Copy

　'書式のみ貼り付け
　Worksheets("Sheet1").Range("B4").PasteSpecial Paste:=xlPasteFormats

　'コピーモード解除
　Application.CutCopyMode = False

End Sub

▽実行後
B2セルをコピーしてB4セルへ「xlPasteFormats」で書式のみ貼り付けを行いました。
セルの塗りつぶしの青色がコピーされているのが分かります。

ExcelVBA-PasteSpecialメソッド-書式のみ貼り付けで貼り付けた後の状態
スポンサーリンク

PasteSpecial（数式のみ貼り付け）

数式のみ貼り付けは「xlPasteFormulas」を使用します。※値や書式は貼りつきません。

.PasteSpecial Paste:=xlPasteFormulas

Sub PasteSpecialTest()

　Worksheets("Sheet1").Range("B2").Copy

　'数式のみ貼り付け
　Worksheets("Sheet1").Range("B4").PasteSpecial Paste:=xlPasteFormulas

　'コピーモード解除
　Application.CutCopyMode = False

End Sub

▽実行後
B2セルをコピーしてB4セルへ「xlPasteFormulas」で数式のみ貼り付けを行いました。
B2セルの数式がコピーされ、B4セルには「D4+E4」（30+40）の式が貼りつき、計算結果の「70」が表示されています。

ExcelVBA-PasteSpecialメソッド-数式のみ貼り付けで貼り付けた後の状態
スポンサーリンク

その他のPasteSpecial

上で紹介したものがよく使用するものですが、PasteSpecialで指定できる貼り付け方法には以下のものがあります。

Sub PasteSpecialTest()

    Worksheets("Sheet1").Range("B2").Copy
    
    'すべて貼り付け
    Worksheets("Sheet1").Range("B4").PasteSpecial Paste:=xlPasteAll
    
    '値のみ貼り付け
    Worksheets("Sheet1").Range("B6").PasteSpecial Paste:=xlPasteValues
    
    '書式のみ貼り付け
    Worksheets("Sheet1").Range("B8").PasteSpecial Paste:=xlPasteFormats
    
    '数式のみ貼り付け
    Worksheets("Sheet1").Range("B10").PasteSpecial Paste:=xlPasteFormulas
    
    'コメントとメモ
    Worksheets("Sheet1").Range("B12").PasteSpecial Paste:=xlPasteComments
    
    '入力規則
    Worksheets("Sheet1").Range("B14").PasteSpecial Paste:=xlPasteValidation
    
    'コピー元のテーマを使用してすべて貼り付け
    Worksheets("Sheet1").Range("B16").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    
    '罫線を除くすべて
    Worksheets("Sheet1").Range("B18").PasteSpecial Paste:=xlPasteAllExceptBorders
    
    '列幅
    Worksheets("Sheet1").Range("B20").PasteSpecial Paste:=xlPasteColumnWidths
    
    '数式と数値の書式を貼り付け
    Worksheets("Sheet1").Range("B22").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    
    '値と数値の書式を貼り付け
    Worksheets("Sheet1").Range("B24").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    'コピーモード解除
    Application.CutCopyMode = False

End Sub
スポンサーリンク

行列を入れ替えて貼り付け

横に並ぶデータをコピーして、縦型にして貼り付けたい、またはその逆を行いたい場合は、PasteSpecialのうしろに「,Transpose:=True」を追加します。

▽実行前
　A1セル～E1セルまで横並びにデータが入っています。

ExcelVBA-PasteSpecialメソッド-行列を入れ替えて貼り付けの実行前
Sub PasteSpecialTest()

    Worksheets("Sheet2").Range("A1:E1").Copy
    
    '行列を入れ替えてすべて貼り付け
    Worksheets("Sheet2").Range("A3").PasteSpecial Paste:=xlPasteAll, Transpose:=True

　 'コピーモード解除
　 Application.CutCopyMode = False

End Sub

▽実行後
１行目の横並びのデータがA3セルに貼り付けられ、縦並びに貼り付けられました。

ExcelVBA-PasteSpecialメソッド-行列を入れ替えて貼り付けの実行後
スポンサーリンク

空白セルをスキップして貼り付け（スキップブランク）

コピー元範囲の中に空白のセルがある場合、空白のセルを除いて貼り付けを行いたい場合は、PasteSpecialのうしろに「, SkipBlanks:=True」を追加します。

▽実行前
　A1セル～A5セルまでをコピーしますが、A3セルは空の状態です。
　わかりやすくするように、貼り付け先のB列の３行目にはあらかじめ「メロン」と入れておきます。

ExcelVBA-PasteSpecialメソッド-空白セルをスキップして貼付けの実行前
Sub PasteSpecialTest()

    Worksheets("Sheet2").Range("A1:E1").Copy
    
    '空白を除いてすべて貼り付け
    Worksheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True

　  'コピーモード解除
　  Application.CutCopyMode = False

End Sub

▽実行後
　B列に貼り付けられましたが、B3セルの「メロン」は残ったままになっています。
　A3セルが空白セルのため貼り付けがスキップされたことが分かります。

ExcelVBA-PasteSpecialメソッド-空白セルをスキップして貼付けの実行後

　↓空白セルをスキップしない場合の実行後
「SkipBlanks:=False」で実行した場合は指定しない場合と同じですべて貼り付けられます。B3セルの値のメロンが空白セルに置き換わっていることが分かります。

ExcelVBA-PasteSpecialメソッド-空白セルをスキップししない場合の貼付けの実行後
スポンサーリンク

コピーモード解除

コピーを行うとコピー内容がクリップボードにコピーされ、コピー元のセル範囲の枠が点滅します。
貼り付け後もこの点滅は点滅したままで、まだ他の場所へ貼り付けることができる状態になっています。

何箇所にも貼り付けを行いたい場合以外は、コピーモードは都度解除してクリップボードの値を空にすることがおすすめです。特に大量コピーの場合などはコピーモードを解除せずに進むと処理が重くなり、エラー停止してしまうこともあります。

　’コピーモード解除
　Application.CutCopyMode = False

Sub PasteSpecialTest()

　Worksheets("Sheet1").Range("B2").Copy

　'すべて貼り付け
　Worksheets("Sheet1").Range("B4").PasteSpecial Paste:=xlPasteAll

　'コピーモード解除
　Application.CutCopyMode = False

End Sub
スポンサーリンク

※コードのコピー利用について

・コードのコピーは自由におこなっていただけます。
・気を付けて作成はしていますがコードには誤りがある可能性があります。
・自身の環境で動作確認をしていますが、すべての方の環境で同様に動くことは保証できません。
・データの破損等の責任は負いかねますのでご自身の責任のもとお使いください。

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