ExcelVBAで「Rangeメソッドは失敗しました」というエラーが出た

ExcelVBAのちょっと変わった仕様に振り回された。

アクティブシートのデータを別のシートにコピーする処理で、「Range’メソッドは失敗しました:’_Worksheet’オブジェクト」というエラーが出た。

原因を特定するのにけっこう時間を使ったので、メモ。

問題のコード

Dataシートの1列目のデータをOutputシートの1列目にコピーするコード。

実行時はDataシートを選択。

Dim LastRow As Long
Dim ws1 As Worksheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'データ貼り付け先シートを指定する
Set ws1 = ActiveWorkbook.Worksheets("Output")
With Worksheets("Data")
    .Range(.Cells(1, 1), .Cells(LastRow, 1)).Copy  Destination:=ws1.Range(Cells(1, 1), Cells(LastRow, 1))
End With

エラーが出たのは下の箇所。

 .Range(.Cells(1, 1), .Cells(LastRow, 1)).Copy  Destination:=ws1.Range(Cells(1, 1), Cells(LastRow, 1)) 

一見すると特に問題がないように思える。

原因

ExcelVBAの仕様で、単にCellsやRangeと書いた場合アクティブシートから範囲を取得するとのこと。

learn.microsoft.com以下、リンク先本文から引用。

オブジェクト修飾子を指定せずにこのプロパティを使用すると、ActiveSheet.Range のショートカットとなります。つまり、アクティブ シートから範囲を取得します。アクティブ シートがワークシートでない場合、このプロパティは失敗します。

そのため、エラーが出た箇所は以下のコードと同じ意味になる。

 Destination:=ws1.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastRow, 1)) 

このマクロはDataシートを選択して実行していたので、エラーの出た箇所では「Dataシートの内容をOutputシートのDataシートのセルにコピーする」という訳の分からないことになってしまっていた。

対応

例えば、以下のように事前にOutputシートをアクティベートしておく。

Set ws1 = ActiveWorkbook.Worksheets("Output")
ws1.Activate
With Worksheets("Data")
    .Range(.Cells(1, 1), .Cells(LastRow, 1)).Copy  Destination:=ws1.Range(Cells(1, 1), Cells(LastRow, 1))
End With

この記述を追加することで、アクティブシートがOutputシートになるためエラーが出なくなる。

DataシートはWithステートメントですでに指定しているため、アクティブシートでなくても問題ない。

おわりに

ExcelVBAって広く使われている割にちょっと変わった仕様が多い気がする。

参考にどうぞ。