ExcelVBAでシートをアクティブにしてからじゃないとエラーになるSheets(2).Range(Cells(1,1),Cells(3,3))=myData

Question

VBAでシートをアクティブにしないとエラーになるのはなぜ？

ExcelVBAで次のようなコードを書きました。(マクロはすべてSheets(1)がアクティブ状態で実行するものとします)

 Sub test1()
   Dim myData
   myData = Range("A1").CurrentRegion.Value
   Dim r As Long, c As Long
   r = UBound(myData, 1)
   c = UBound(myData, 2)
   Sheets(2).Range(Cells(1, 1), Cells(r, c)).Value = myData
 End Sub

現在Sheets(1)がアクティブになっている状態で、Sheets(1)のある範囲のセルの値を非アクティブのSheets(2)にコピペするといったものです。

Sheets(2).Range(Cells(1, 1), Cells(r, c)).Value = myData

ここでエラーが発生します。

実行時エラー１００４
アプリケーション定義またはオブジェクトの定義エラーです。

次のように変更すれば解決はします。

 Sub test2()
   Dim myData
   myData = Range("A1").CurrentRegion.Value
   Dim r As Long, c As Long
   r = UBound(myData, 1)
   c = UBound(myData, 2)
   Sheets(2).Activate
   Range(Cells(1, 1), Cells(r, c)).Value = myData
 End Sub

要するに、一旦Sheet(2)をアクティブにしてから範囲に値を入れているわけです。

そこで疑問なのですが、なぜわざわざSheet(2)をアクティブにしてからじゃないとエラーになってしまうのでしょうか？

たとえば、以下のようなコードではエラーになりません。

Sub test3()
  Dim myData
  myData = Range("A1:B5").Value
  Sheets(2).Range("A1:B5") = myData
End Sub

Sub test4()
  Dim myData
  myData = Range("A1").Value
  Sheets(2).Cells(1, 1) = myData
End Sub

エラーになるコードとの違いはセル範囲かつRangeの始点と終点にcellsを使っているという点だと思うのですが、なぜその場合はエラーになってしまうのか理由が知りたいです。

よろしくおねがいします。

Answer 1

屋上屋を架すようですが、別解を記してみます。個人的には３つ目の書き方をすることが多いです。

Sheets(2).Range(Cells(1, 1).Address, Cells(r, c).Address).Value = myData
Sheets(2).Cells(1, 1).Resize(r, c).Value = myData
Sheets(2).Cells.Resize(r, c).Value = myData

Comments

[siorukuru]

参考になります。
ありがとうございます！

Answer 2

すでに解決策は出ていますが、

Sheets(2).Range(開始セル, 終了セル) とする場合、開始セル、終了セルはSheets(2)のセルではないとダメということです。

Sheets(2).Range(Cells(1, 1), Cells(r, c)) というようにシートを指定しないとActiveSheetが対象になりますので、
Sheets(2).Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(r, c)) という意味になり、Sheets(2)がアクティブでない場合はエラーになります。

Sheets(2)が繰り返し出現するのを避けるために、下記のように書く場合が多いですね。

With Sheets(2)
    .Range(.Cells(1, 1), .Cells(r, c)).Value = myData
End With

Comments

[siorukuru]

なるほど納得です。
詳しい解説ありがとうございます！

Answer 3

次のようにすればエラーにならないです。

Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(r, c)).Value = myData

Comments

[siorukuru]

なるほど、、、
なんとなくわかりました。