【ExcelVBA Tips】セルの書式と数式のみをコピーする

morishita

先日ExcelVBAで表から書式や数式のみをコピーするマクロを作成しようとしたのですが、
その際に気が付いた点や実際に作成したサンプルをご紹介します。

※今回使用するファイルはこちら ⇒ ダウンロード

【やりたいこと】

①元の表(下図の”B2:D7″の表)の、書式(罫線や背景色)と数式のみをコピー。
②他の場所(今回は”F2″セル)にペーストして複製。

※なお、元の表のB列のセルには下図の様に数式が入力されています。

【使用するメソッド】

構文 : Range.Copy( Destination )

セル範囲をクリップボードにコピーします。
引数Destinationの指定があった場合は、指定のセルへの貼り付けも行います。

構文 : Range.PasteSpecial( Paste, Operation, SkipBlanks, Transpose )

対象のセル領域へ、クリップボードの内容をペーストします。
引数Pasteの指定があった場合は、貼り付ける内容を指定することができます。
Pasteに指定する定数の一部を以下に記載します。(他の引数の説明は割愛します。)

定数	内容
xlPasteAll（既定）	全て
xlPasteFormulas	数式
xlPasteValues	値
xlPasteFormats	書式
xlPasteValidation	入力規則

【マクロを作成 (失敗Ver.)】

ここから本題に入っていきます。
「CopyとPasteSpecialメソッドを使えばうまくいくのでは？」と思い、まず書いたのが以下のマクロです。

Sub test1()    
    Range("B2:D7").Copy  ' セル範囲をクリップボードに貼り付け
    
    Range("F2").PasteSpecial Paste:=xlPasteFormats    ' 書式のペースト
    Range("F2").PasteSpecial Paste:=xlPasteFormulas   ' 数式のペースト
    
    Application.CutCopyMode = False
End Sub

マクロの動作イメージは、以下の通り。

1. 表領域を丸ごとコピー。(2行目)
2. 書式のみ貼り付け。(4行目)
3. 数式のみ貼り付け。(5行目)

うまくいきそうな感じがするので早速実行してみた結果が下図です。

書式も数式もコピーされていてなんとなくうまくいった気がしそうですが、すぐに以下の問題に気付きます。

問題点：見出し行や表の中の値までコピーされてしまっている！！

この原因を調べてみると、以下の箇所が原因となっていました。

原因：
PasteSpecialメソッドで「xlPasteFormulas」を指定した際の動作は、
数式が入っていれば数式を、値が入って入れば値を貼り付ける、という処理になっている。

「xlPasteFormulas」を指定すると数式以外は無視してくれるかと思っていたのが誤りで、
実際には上記処理となるために想定した結果が得られませんでした。

…というわけで、以下では別の処理を考えていきます。

【マクロを作成 (成功Ver.)】

別の方法を考えるに当たって、活用したのが以下のメソッドです。

構文 : Range.SpecialCells( Type, Value )

親に指定したRangeオブジェクトの中で、Typeで指定した条件と一致する全てのセルへの参照(Rangeオブジェクト)を返します。
Typeに指定する定数の一部を以下に記載します。(引数Valueの説明は割愛します。)

定数	内容
xlCellTypeBlanks	空白セル
xlCellTypeConstants	定数
xlCellTypeFormulas	数式
xlCellTypeVisible	可視セル
xlCellTypeAllFormatConditions	表示形式が設定されているセル

上記定数の中で目を引くのが「xlCellTypeFormulas」です。
早速上記メソッドを使用して書き換えたマクロが以下となります。

Sub test2()
    Range("B2:D7").Copy                               ' セル範囲をクリップボードに貼り付け
    Range("F2").PasteSpecial Paste:=xlPasteFormats    ' 書式のペースト

    Range("B2:D7").SpecialCells(xlCellTypeFormulas).Copy  ' 数式の入力されているセルのみコピー
    Range("F3").PasteSpecial Paste:=xlPasteFormulas       ' 数式のペースト
    
    Application.CutCopyMode = False
End Sub

マクロの中身ですが今回は以下の流れを想定して作成しました。

1. 表領域を丸ごとコピー。(2行目)
2. 書式のみ貼り付け。(3行目)
3. 表領域のうち、数式の入力された箇所のみをコピー。(5行目)
4. 数式のみ貼り付け。(6行目)

この中の手順3のところで「SpecialCells」メソッドを使用しています。
そして気になる実行結果が下図となります。