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

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

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


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


セルのコピーの基本

セルのコピーの基本については、以下のページ内容を把握しておいてください。
第40回.セルのコピー・カット&ペースト(Copy,Cut,Paste)
・セルをコピー(複写)する場合 ・セルを切り取る(移動する)場合 ・セル範囲のコピーについて ・別のシートにコピーする場合 ・アクティブシート以外へのコピー ・セルのコピーについてのサイト内参考ページ
第41回.セルのコピー&値の貼り付け(PasteSpecial)
・PasteSpecialメソッド ・値の貼り付け ・いろいろなコピーのVBAの書き方 ・PasteSpecialの使用例 ・最後に
第42回.セルをコピーするとは
・セルをコピーするとは ・上記方法ではコピーできないプロパティ ・.Valueのセル範囲間のコピー ・.Value以外の場合は、セル範囲をセル範囲にコピーは出来ません ・コピー方法の使い分け ・セルのコピー(Copyメソッド)実行時の注意点 ・最後に

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

ごく普通のコピー、といいますか、自動記録したお勧めできない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技術解説」の記事

標準モジュールとシートモジュールの違い

・書かれている場所と概要 ・シートを省略してRangeやCellsと記述した時 ・他モジュールから使う時 ・デバッグ時の違い ・標準モジュールとシートモジュールの使い分け方
オートフィルタ(AutoFilter)の使い方まとめ
オートフィルタはエクセルの中でもデータ処理において非常に強力なものです、特に大量データの処理には書くことのできない機能となっています。しかし、使い方が難しく、またバージョン違いの影響が大きく、使いずらい物となっていて、問い合わせを受ける事も多いです。
複雑な条件(複数除外等)のオートフィルター(AutoFilter)
・"A","C"で絞り込む場合 ・"A","C"以外で絞り込む場合 ・"A","C","E"以外で絞り込む場合 ・作業列を追加
クリップボードを使わないセルのCopy
Rangeの使い方:最終行まで選択を例に
Rangeの使い方・書き方について、データ最終行まで選択する場合を例に説明します、Rangeの書き方なので、RangeオブジェクトではなくRangeプロパティの解説という事になります。最近続けざまに、以下のようなコードを見かけました。Range("A2",Range("A2"…
フルパスをディレクトリ、ファイル名、拡張子に分ける
ファイルのフルパスを、ディレクトリ、ファイル名、拡張子に分けます。FileSystemObjectを使う方法と、VBA関数(InStrRev)で分けるVBAのサンプルコードになります。FileSystemObject 順に、C:\Users\hogehoge\Desktop サンプル.txt サンプル txt と表…
Colorプロパティの設定値一覧(カラー定数、XlRgbColor列挙)
・Excelのカラーについて ・ColorIndex、カラー定数、XlRgbColor列挙、RGB値 ・システム カラーの定数 ・サイト内の色関連関連ページ
VBAを定型文で覚えよう
・サンプルVBA ・サンプルVBAの解説 ・VBAを定型文で覚えるための課題 ・VBAを定型文で覚えることの目標
VBAこれだけは覚えておきたい必須基本例文10
・1.最終行まで処理 ・2.後ろから逆順ループ ・3.コレクション処理 ・4.分岐処理 ・5.セルのコピー ・6.VBA関数とワークシート関数 ・7.オブジェクト変数とWith ・8.ブックを開く・閉じる ・9.ファイル一覧 ・10.テキスト読み書き ・VBA必須基本例文10を覚えた後は
エクセルVBAでのシート指定方法
・シートのインデックス番号で指定 ・シートの名称で指定 ・シートのオブジェクト名で指定 ・ブックの保護 ・VBAでのシート指定方法
文字列結合&でコンパイルエラーになる理由
・&の記述がエラーになる入力とは ・&の前後にはスペースが必要な時がある理由 ・&の他では^(キャレット)でも同様の事がありえます ・最後に


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

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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