VBAでコピーを別のシートに行う

「vba セル 範囲 コピー シート間 アクティブにしない」
という検索キーワードで、このサイト『インストラクターのネタ帳』へのアクセスがありました。

セル範囲を、アクティブシートの切り替えを行わずに、シート間でコピー・アンド・ペーストするExcel VBAのコードを調べていた方による検索です。

「vba コピー 別シートに貼り付け」
「エクセルマクロ 別シートへコピー」
といった検索キーワードでのアクセスもあります。

セルのコピー＆貼り付けを別シートに行うサンプル

以下のマクロを実行すると、アクティブなシートのA1:C5セルが、「コピー先」という名前のワークシートのA1:C5セルに、コピー＆貼り付けされます。

RangeオブジェクトのCopyメソッドで、貼り付け先を指定する引数Destinationを指定すると、

貼り付け先シートを選択しなくてもコピペができます。

• オブジェクトブラウザーの使い方を丁寧に解説した教本

コピー元をシートから指定してセル範囲を別シートにコピーするサンプル

コピー元のほうもシートから指定する場合は、以下のようなマクロです。

上記のマクロなら、アクティブシートが何であっても、「コピー元」ワークシートのA1:C5セルが「コピー先」ワークシートのA1:C5セルにコピー＆貼り付けされます。

コピー元をシートから指定してCellsプロパティを使ってセル範囲を別シートにコピーする際にエラーが発生するケース

問題になるのはコピー元のセル範囲を指定する際に、Cellsプロパティを組み合わせる場合です。

以下のようなマクロにすると、コピー&ペーストが上手くいく場合と、
「実行時エラー'9':インデックスが有効範囲にありません。」
といったエラーが発生する場合が出てきます。

「コピー元」ワークシートがアクティブなときに上記マクロを実行すれば、問題なくコピペが行われますが、「コピー元」ワークシートがアクティブでない場合にはエラーとなってしまいます。

コピー元をシートから指定してCellsプロパティを使ってセル範囲を別シートにコピーする際にエラーが発生する理由

Cellsプロパティは上位のオブジェクト式を省略した場合、アクティブなワークシート上のセルを表すRangeオブジェクトを返してきます。

ですから、「コピー元」シートがアクティブなときには「Sheets("コピー元").Range(Cells(1, "A"), Cells(5, "C"))」というオブジェクト式は、「コピー元」シートの、A1:C5セルを表すRangeオブジェクトを取得できます。

が、「コピー元」シートがアクティブでないときには「Cells(1, "A")」「Cells(5, "C")」というオブジェクト式は、実行した時にアクティブになっていたシートのA1セル・C5セルを表すRangeオブジェクトが取得され、そのRangeオブジェクトが「Sheets("コピー元").Range」プロパティの引数に指定されるという、Excelからするとまったく意味がわからないオブジェクト式になってしまうため、実行時エラーが発生します。

コピー元をシートから指定してCellsプロパティを使ってセル範囲を別シートにコピーするサンプルマクロ

Cellsプロパティも上位のオブジェクト式を省略せずに記述すれば、実行時エラーは発生しなくなります。

どうしても長いオブジェクト式になってしまいますので、オブジェクト変数か、Withステートメントを使うほうがいいでしょう。

オブジェクト変数を使う場合は以下のとおりです。

Withステートメントを使う場合は以下のとおりです。

Cellsプロパティの前の「.」がポイントです。

最終更新日時：2020-09-12 13:41