PR

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

VBA
2024.02.23

Excel VBAの「PasteSpecial」メソッドの使用例をご紹介します。

スポンサーリンク

コピー前の状態

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

スポンサーリンク

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」が表示されています。

スポンサーリンク

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セルに貼りつけられています。

スポンサーリンク

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」で書式のみ貼り付けを行いました。
セルの塗りつぶしの青色がコピーされているのが分かります。

スポンサーリンク

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」が表示されています。

スポンサーリンク

その他の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セルまで横並びにデータが入っています。

Sub PasteSpecialTest()

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

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

End Sub

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

スポンサーリンク

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

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

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

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セルが空白セルのため貼り付けがスキップされたことが分かります。

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

スポンサーリンク

コピーモード解除

コピーを行うとコピー内容がクリップボードにコピーされ、コピー元のセル範囲の枠が点滅します。貼り付け後もこの点滅は点滅したままで、まだ他の場所へB2セルの内容をコピーすることができます。継続して貼り付けを行いたい場合以外は、コピーモードは都度解除してクリップボードの値を空にすることがおすすめです。特に大量コピーの場合などはコピーモードを解除せずに進むと処理が重くなり、エラー停止してしまうこともあります。

Sub PasteSpecialTest()

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

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

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

End Sub
スポンサーリンク

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

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

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