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技術解説」の記事

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


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

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作




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


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


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