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


先日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」メソッドを使用しています。
そして気になる実行結果が下図となります。

今回はうまくいきました!!

※念のための注意点として、5行目でのコピー範囲は下図のように「B3:B7」となるので、
「test1」の時と違って貼り付け先が「F3セル」となっている事に注意してください。

※基準となるセル(今回の「F3セル」みたいなセル)が不明で、表自体の相対的な移動距離(今回だと4列右にずらす)
などがわかっているのであれば、以下のような記述もできるので参考にしてください。(実行結果は同じです。)

参考プログラム

【まとめ】

というわけで、今回のポイントをまとめてみます。

① 書式のコピー
・表全体をコピーした後、PasteSpecialメソッドで書式のみペースト。
⇒ 意図した通りに動作。

② 数式のコピー
・表全体をコピーした後、PasteSpecialメソッドで数式のみペースト。
⇒ 値の入っているセルまで貼り付けられてしまう。
・表全体の中で、数式の入ったセルのみをコピーしてペースト(SpecialCellsメソッド使用)
⇒ 今回の意図通りに動作したのでこちらを採用。

以上、Excelを使っていて「数式のみコピーしたい!」というケースになったときに参考にして頂ければ幸いです。

■ExcelVBA関連記事

  • このエントリーをはてなブックマークに追加

PAGE TOP