VBA：Selection.Copyでエラーが出る場合はUsedRange.Copyに置き換えた方が安全

IT知識

2020.07.10

昔エクセル2003で作ったVBAマクロをエクセル2016で実行していたら、動作するにはするのだが、非常に遅くなってしまった。いつまでたってもセル範囲のコピーが終了しないのだ。何度かマクロを実行するうちにとうとう「メモリ不足です」なるエラーがでて強制終了してしまった。

原因は Selection.Copy 。
エラーが出たわたしのマクロ部分はこちら。（前後は省略）

    :
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
    :

エクセル2003の頃は、ワークブックのサイズが小さかったので例えばセル範囲を全て選択して別のワークシートに貼り付けも問題なかったのだろう。最近のエクセルではワークシート1枚あたりのセル範囲が増えて容量が巨大になったのだ。

Selection.Copy を 《ワークシート名》.UsedRange.Copy に置き換えて実行したらすんなり実行できた。

    Sheets("対象のワークシート名").UsedRange.Copy '使っているセル部分のみコピー
    Workbooks.Add
    ActiveSheet.Paste

UsedRange.Copyは、使っているセル範囲のみをクリップボードにコピーしてくれる機能を持つ。ただし、対象となるワークシート名を指定する必要がある。

例）”名簿”というワークシートの使っているセル範囲のみをコピーする場合

Sheets("名簿").UsedRange.Copy

全て選択の「全て」の容量も時代により変わるのですね。