Home » ExcelVBA Rangeオブジェクト » セルのコピーをシート間で行うExcelマクロ

セルのコピーをシート間で行うExcelマクロ

対象:Excel2007, Excel2010, Excel2013, Windows版Excel2016

セルのコピーをシート間で行うExcelマクロ

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

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

[スポンサードリンク]

セルのコピーをシート間で行うサンプルマクロ

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


Sub セルのシート間コピー()
 Range("A1", "C5").Copy _
   Destination:=Sheets("コピー先").Range("A1")
End Sub

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

セルのコピーをシート間で行うExcelマクロ

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

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

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


Sub セルのシート間コピー_コピー元をシートから指定()
 Sheets("コピー元").Range("A1", "C5").Copy _
   Destination:=Sheets("コピー先").Range("A1")
End Sub

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

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

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

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


Sub セルのシート間コピー_コピー元をシートから指定_Cells_エラーあり()
 Sheets("コピー元").Range(Cells(1, "A"), Cells(5, "C")).Copy _
   Destination:=Sheets("コピー先").Range("A1")
End Sub

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

コピー元をシートから指定して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プロパティも上位のオブジェクト式を省略せずに記述すれば、実行時エラーは発生しなくなります。


Sub セルのシート間コピー_コピー元をシートから指定_Cells()
 Sheets("コピー元").Range(Sheets("コピー元").Cells(1, "A"), Sheets("コピー元").Cells(5, "C")).Copy _
   Destination:=Sheets("コピー先").Range("A1")
End Sub

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

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


Sub セルのシート間コピー_コピー元をシートから指定_Cells_オブジェクト変数()
 Dim sh_org As Worksheet
 Dim sh_dst As Worksheet

 Set sh_org = Sheets("コピー元")
 Set sh_dst = Sheets("コピー先")

 sh_org.Range(sh_org.Cells(1, "A"), sh_org.Cells(5, "C")).Copy _
   Destination:= sh_dst.Range("A1")
End Sub

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


Sub セルのシート間コピー_コピー元をシートから指定_Cells_With()
 With Sheets("コピー元")
  .Range(.Cells(1, "A"), .Cells(5, "C")).Copy _
    Destination:=Sheets("コピー先").Range("A1") End With
End Sub

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

[スポンサードリンク]

Home » ExcelVBA Rangeオブジェクト » セルのコピーをシート間で行うExcelマクロ

TrackBack:1

TrackBack URL
Sheets・Worksheetsでインデックスが有効範囲にありませんエラーの発生する原因 from インストラクターのネタ帳
Sheets・Worksheetsで「インデックスが有効範囲にありません。」エラーの発生する原因について解説しています。

Home » ExcelVBA Rangeオブジェクト » セルのコピーをシート間で行うExcelマクロ

「ExcelVBA Rangeオブジェクト」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.