Excel VBA コピーしたデータから定数だけを貼り付けるマクロ基礎機能を使いこなす

はじめに

今回は、
Excelのセル範囲をコピーしたデータから、計算式の部分を避けてベタ打ち定数の部分だけを貼り付ける
処理について扱う。

と言ってもまるで分かりにくいだろうから、まず例題を示すけど、面倒なら考え方とかマクロソースコードの記述に飛んでもらえれば良いかと思う。

今回の例題で使用するExcelファイルは、公開フォルダに保存しており、その中の「貼付け.xlsm」にマクロも保存してある。

例題

下図の表は、「貼付け.xlsm」というExcelファイルに、「貼付けTBL」というテーブルを作成したものだ。

=[@1月実績]-[@1月目標]

という計算式を「1月実績」の列に入れてあるが、「2月実績」「3月実績」の列にも同様に、＜実績－目標＞の計算式が入れてある。

そもそも「テーブル」って何かとか、この
=[@1月実績]-[@1月目標]みたいな数式の書き方とかについては、最後にまとめときます。

で、この「貼付けTBL」は、数式は入れてあるけど実績・目標が空欄になってて、これを下図の「コピーTBL」から貼り付けたい。

この「コピーTBL」は、事務担当者に記入してもらったシートだと思えば良い。
それを、空白にしておいたまとめシートである「貼付けTBL」に貼付け直すイメージだ。具体的にはB2セル～J6セルをコピーし貼付けることになる。

・・・のだが、ここで「貼付けTBL」に単なる貼付け(Ctrl+V)を実行すると、差異欄の計算式がエラいことになる。
下図のように、他ブックへのリンクが入ってしまうわけだ。

これを恐れて値のみ貼付けをしたら、当然ながら計算式が消えてしまう。かと言って
B2～C6セルで1回、E2～F6セルで1回、H2～I6セルで1回
などと、定数(ベタ打ち)のセル範囲だけを1回1回選んで値のみ貼付けを繰り返すというのは、ナンセンスだ。

今回はテーブルを扱った例で示しているが、別にテーブルでなくてもExcelデータにおいては、
「うかつに計算式ごと貼り付けると他ブックへのリンクなどが混じってしまうが、値のみ貼り付けると計算式が消えてしまう」
ジレンマはしょっちゅう起こることだ。

Excel2010以降超基本機能～値のみ貼付け～ 0.3秒で操作できるようになろう

2020年3月21日

ちなみに、他ブックへのリンクが混じると何がマズいのか自体が分からないとなると、もはや給料泥棒状態なので、それは感覚でマズいと思うようになろう。

元のExcelブックが消えたり移動されたりしたとき、計算式がどうなるか分かったものじゃないし、元のブックと連関性を保ち続けることがまずできないだろう。

処理目的まとめ

というわけで今回の課題を例題に沿って示すと、

「コピーTBL」のB2セル～J6セルをコピーして、「貼付けTBL」のB2セルをクリックする
までの手順は固定とする。

そこから、コピーしたデータを活用して、「貼付けTBL」の数式を消さないようにサクッと目標・実績の数値を値のみ貼付けしたい

ということだ。

もう少しクドく言うと、

ベタ打ちの数字や計算式が混じったセル範囲をコピーするが、その中で計算式の部分は避けて、ベタ打ち数字の部分だけを都合良く貼り付けたい。

ということだ。

考え方

今回の課題の解決は、私が考える限りマクロじゃないとイチイチやってられないが、それは二の次だ。

今回大事なのは、Excelの通常機能によって解決するならどのようにするかだ。

マクロを使わない基本機能のみの操作で、どのようなコマンドを組み合わせ対処すれば良いか、考えてみよう。

その操作を高速化するためにマクロに記述はするが、あくまで基本操作による解決方法を先に考えることにしたい。
だからマクロのソースコードは、後でオマケとして書く。

手順

ではまず、今回コピーした「コピーTBL」B2セル～J6セルのデータを、いったん作業用シートに、「数式のみ貼付け」で貼付ける。
(課題の条件に、「貼付けTBL」のB2セルをクリックするまでの手順は固定と書いたが、この作業用シートを使う作業は、そのB2セルクリック後にバックグラウンドで実行する手順と考えてほしい)

エラーとかも混じるが、貼り付けた作業用シートがこんな感じになる。
セル範囲としてはA1～I5セルとなるが、これは控えて覚えておく。

そしてこのセル範囲中で、数式の入ったセルをジャンプ機能で全て選んでDeleteキーを押そう。
そうすれば、数式を消してベタ打ちのセルだけを残すことができる。

なお数式の入ったセルへのジャンプ機能は、まどろっこしい操作をせずクイックアクセスツールバーからワンタッチでできるように設定しておこう。

Excel「ジャンプ」機能について(1)推奨方法を述べる

2020年3月21日

この作業用シートの、先ほど控えておいたA1～I5セルをコピーする。
そして、貼付け先の起点である「貼付けTBL」のB2セルをクリックする。

問題は次をどうするかだが、ここで「形式を選択して貼り付け」を起動しよう。
ショートカットキーはCtrl+Alt+Vだ。

そして「値」「空白セルを無視する」の2つにチェックを入れて「OK」を押そう。

するとデータが貼り付くが、「貼付けTBL」の計算式は損なわれず、上手いこと目標・実績のセルだけに値がセットされてくれる。

改めて今回の処理のポイントを述べると、次のようになる。

定数の部分だけを貼り付ける処理方法のポイント

作業用シートの作業セルにて、計算式の部分を削除した(=空白にした)セル範囲を作り、それをコピーする。
貼付け時に「空白セルを無視する」を指定することで、もともと計算式が入っていた部分だけを都合良く無視した貼付けが実現できるようになる。

Excel「形式を選択して貼り付け」機能の便利技

2020年3月21日

マクロのソースコード

それでは今回の課題を解決するマクロのソースコードを示す。

繰り返すが、今回大事なのはマクロの方ではなく、基本操作をどのように組み合わせるかの方だ。

このマクロは、その基本操作を再現しただけのものなので、詳説も省略する。

Option Explicit

Sub pasteValueWithoutFormulas()
'値のみ貼付けして尚且つ、途中に計算式の入ったセルがあったら無視する。
'データをコピー済で、貼付けの起点セルを選択した直後に起動する前提のマクロです。
    '貼付けの起点セル
    Dim pasteRng        As Range: Set pasteRng = Selection(1)
    
    '作業用シート
    Dim tmpWs           As Worksheet: Set tmpWs = Worksheets.Add
    
    '既にデータがコピーされているという前提のマクロで、エラー対応は適当です
    On Error GoTo fin
    
    '数式のみ貼付け
    tmpWs.Cells(1, 1).PasteSpecial xlPasteFormulas
    Call appSet '描画等省略
    
    '作業用シートの、データが貼付けられたセル範囲
    Dim tmpRng      As Range: Set tmpRng = Selection
    
    '数式の入ったセル範囲を消した上で、コピー
    tmpRng.SpecialCells(xlCellTypeFormulas).ClearContents
    tmpRng.Copy
    
    '数式セルが削除された上での、空白セルを無視した値のみ貼付け
    pasteRng.PasteSpecial Paste:=xlPasteValues, skipblanks:=True
    
fin:
    tmpWs.Delete '作業用シートを削除
    Call appReset '描画等再開
End Sub

Sub appSet()
'マクロ処理中に、描画など余計なものを省略して高速化
    With Application
        .ScreenUpdating = False '描画を省略
        .Calculation = xlCalculationManual '手動計算
        .DisplayAlerts = False '警告を省略。
    End With
End Sub

Sub appReset()
'描画などの設定をリセット
    With Application
        .ScreenUpdating = True '描画する
        .Calculation = xlCalculationAutomatic '自動計算
        .DisplayAlerts = True '警告を行う
    End With
End Sub