VBA技術解説
クリップボードを使わないセルのCopy

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-01-21

クリップボードを使わないセルのCopy


セルをコピーすることはマクロVBAにおいてもっとも基本的なことですが、
セルをコピーすると、クリップボードが使われてしまうので、他の作業との併用時に困ることが多々あります。
そこで、クリップボードを使わずに、セルをコピーする方法を考えてみましょう。


セルのコピーの基本

セルのコピーの基本については、以下のページ内容を把握しておいてください。
第40回.セルのコピー・カット&ペースト(Copy,Cut,Paste)
あるセルをコピーまたはカットして、別のセルに貼り付けるVBAま説明です。セルを同じシートの別のセルにコピーしたり、セルを別のシートにコピーしたりするVBAになります。手作業で、セルをコピー(Ctrl+C)またはカット(Ctrl+X)して、他のセルに貼り付け(Ctrl+V後にESCまたはEnter) これと同じ動作をするVBAになります。
第41回.セルのコピー&値の貼り付け(PasteSpecial)
値の貼り付けと題しましたが、値だけではなく、「形式を選択して貼り付け」のいろいろな指定方法です。セルをコピーして、他のセルに「形式を選択して貼り付け」する場合のマクロVBAコードです。セルの値や書式を別のセルコピーすることはマクロVBAでは定番かつ必須の技術になります。
第42回.セルをコピーするとは
セルをコピーするとは、どういう事でしょうか… セルをコピーするというマクロVBAを少し掘り下げて考えることで、より実践的なマクロVBAコードを書くことが出来るようになります。コピーと一言で言っているものは、何のコピーを指しているのでしょうか。

「マクロの記録」のコピー

ごく普通のコピー、といいますか、自動記録したお勧めできないVBAの書き方ですが。

Range("A1").Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

これは間違いなくクリップボードを使っています。
.Copyでクリップボードに入ります。

Destinationを指定したコピー

では次に、Destinationを指定したコピー方法です。

Range("A1").Copy Destination:=Range("B1")

Destination:=は省略可能です。
これもクリップボードが使われています。
先の.Copy、.Paste、CutCopyMode = False、これらを1行で書いているに過ぎません。
つまり、それ以前のクリップボードは消えてしまいますが、実行後のクリップボードには残りません。
1行で済んでいて記述も簡便ですので、セルのコピーでは基本的にはこれを使うようにしましょう。

クリップボードを使わないセルのCopy

実は、クリップボードを使わずにセルの全情報をコピーする方法はありません・・・
基本的には、マクロ実行中は、じっと我慢の子で待っていましょう(笑)
なのですが、それでは本記事の意味がありませんので、方法を検討しましょう。
Range("A1:E10")をRange("G1:K10")にコピーする場合です。

Range("G1:K10").Value = Range("A1:E10").Value

これでセルの値(Value)はクリップボードを使わずにコピーされます。
注意点としては、
・左辺と右辺のセル範囲の大きさを同じにすること
・Valueも省略せずに書くこと

では、他のプロパティである、書式や色をどうするかなのですが、良い方法はありません。
Valueにならって、

Range("G1:K10").NumberFormatLocal = Range("A1:E10").NumberFormatLocal

全てのセルが同一なら良いのですが、バラバラな書式の場合は、これではコピーされません。
しかし、そもそもコピーする全てのセルが同一書式なら、

Range("G1:K10").NumberFormatLocal = Range("A1").NumberFormatLocal

これで良いことになります。
つまり、単一の値をセル範囲に入れているだけです。
Valueだけが特別です。
これは、セル範囲を配列に入れるときと関連しています。

Dim MyAry
MyAry = Range("A1:E10").Value

これで配列が作成されます。
そして、

Range("G1:K10").Value = MyAry

これで値がコピーされることになります。
つまり、

Dim MyAry
MyAry = Range("A1:E10").Value
Range("G1:K10").Value = MyAry

これのMyAryを消し込めば、

Range("G1:K10").Value = Range("A1:E10").Value

このようになるということです。
そして、これができるのはValueだけだと考えてもらえば良いでしょう。

値と書式のコピー

ではValue以外の残りのプロパティをコピーするにはどうしたらよいかですが、
「セルの書式設定」で設定する内容であれば、クリップボードを使用せずに一括でコピーできます。

Range("C1:C3").Value(xlRangeValueXMLSpreadsheet) = Range("A1:A3").Value(xlRangeValueXMLSpreadsheet)

Valueの引数

Rangeオブジェクト.Value (RangeValueDataType)

RangeValueDataTypeには、XlRangeValueDataType列挙を指定します。

名前 説明
xlRangeValueDefault 10 既定値です。指定したRangeオブジェクトが空の場合は、値 empty (このケースについては、IsEmpty 関数を使用してテストします) を返します。Rangeオブジェクトに複数のセルが含まれている場合は、値の配列を返します (このケースについては、IsArray 関数を使用してテストします)。
xlRangeValueXMLSpreadsheet 11 指定した XML スプレッドシート形式のRangeオブジェクトの値、書式設定、数式、名前を返します。
xlrangevaluemspersistxml 12 指定した XML 形式のRangeオブジェクトのレコードセットの表示を返します。
今回のコピーでは使えません。

ただし、これでコピーできる書式は、「セルの書式設定」の範囲内になります。
書式と言っても条件付き書式はコピーされません。
「形式を選択して貼り付け」の「書式」のコピー
.PasteSpecial Paste:=xlPasteFormats
この場合は、条件付き書式もコピーされますので、これの代用にはなりません。
もちろん、入力規則もハイパーリンクもコピーされません。

必要な書式だけをコピーしたい場合は、
セル範囲をForで回して、一つずつのセルについて各プロパティをコピー先に値として代入するしかないです。
記述は簡単ですが、セル範囲が大きいと時間がかかってしまいますが、
一応、以下に各プロパティをコピーする場合のVBAコードを簡単に書いておきます。

Sub sample()
  Call sample_sub(Range("A1:E10"), Range("G1"))
End Sub
Sub sample_sub(fromRange As Range, toRange As Range)
  Dim i As Long
  Dim j As Long
  For i = 1 To fromRange.Rows.Count
    For j = 1 To fromRange.Columns.Count
      toRange.Cells(i, j).Value = fromRange.Cells(i, j).Value
      toRange.Cells(i, j).NumberFormatLocal = fromRange.Cells(i, j).NumberFormatLocal
      toRange.Cells(i, j).Interior.Color = fromRange.Cells(i, j).Interior.Color
      toRange.Cells(i, j).Font.Color = fromRange.Cells(i, j).Font.Color
      toRange.Cells(i, j).Font.Bold = fromRange.Cells(i, j).Font.Bold
    Next
  Next
End Sub

良く使うプロパティは、概ねこのあたりでしょう。
後は罫線がありますが、
これは四辺のプロパティを別々に記述しなければならず少々面倒ですね。

書式以外のコピー

セルのコピーを使わずに書式以外をコピーするとなると、
セル範囲をForで回して、一つずつのセルの情報を取得しつつ、コピー先のセルに設定するしかありません。

ハイパーリンクは割と簡単ですが、
条件付き書式や入力規則となるとかなり大変(いや、とんでもなく大変)なVBAになってしまいます。
クリップボードを消さないというだけのためとしては苦労が大きすぎて現実的とは思えません。

クリップボードを使わないセルのCopyのまとめ

結論としては、以下のような使い分けになります。

セルの全情報

Range("A1").Copy Destination:=Range("B1")
クリップボードは使われます。

値(Value)

Range("G1:K10").Value = Range("A1:E10").Value
クリップボードは使われません。

値と「セルの書式設定」

Range("C1:C3").Value(xlRangeValueXMLSpreadsheet) = Range("A1:A3").Value(xlRangeValueXMLSpreadsheet)
クリップボードは使われません。

Destinationでのコピーはクリップボードが消されてしまいますが、それ以外の不都合が特にありません。
クリップボードをどうしても残したままにしておきたい、かつ、セルの全情報をコピーしたいとなったら、
かなり大変だという事を理解してください。



同じテーマ「マクロVBA技術解説」の記事

標準モジュールとシートモジュールの違い
オートフィルタ(AutoFilter)の使い方まとめ
複雑な条件(複数除外等)のオートフィルター(AutoFilter)
クリップボードを使わないセルのCopy
Rangeの使い方:最終行まで選択を例に
フルパスをディレクトリ、ファイル名、拡張子に分ける
Colorプロパティの設定値一覧(カラー定数、XlRgbColor列挙)
VBAを定型文で覚えよう
VBAこれだけは覚えておきたい必須基本例文10
エクセルVBAでのシート指定方法
文字列結合&でコンパイルエラーになる理由


新着記事NEW ・・・新着記事一覧を見る

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ