ExcelVBA初心者がつまづきがちな「別のシートへの代入時エラー」について、エラーコード例を交えて解説します。（本稿掲載の各コードは同じ処理の書き換えです）

Sub 非アクティブシートへの代入時エラー例()
    Dim 配列()
    Sheet1.Activate
    配列 = Range(Cells(1, 1), Cells(5, 5))
    Sheet2.Range(Cells(1, 1), Cells(5, 5)) = 配列
End Sub

↑のサンプルコードは、実行するとSheet2.Range(Cells(1, 1), Cells(5, 5)) = 配列の行が原因で実行時エラーが発生します。

このコードは「Sheet1のセル範囲の値を動的配列に格納し、Sheet2のセル範囲に代入する」という意図のものですが、問題の箇所ではCellsの親オブジェクトが省略されています。
これにより、OSはCellsの親オブジェクトをActiveSheet(= Sheet1)だと認識するため、
Cellsとその入れ子先であるRangeで親オブジェクトが異なる状態になってしまうことがエラーの原因です。

問題の箇所はOS側には↓のように認識されています。
Sheet2.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(5, 5)) = 配列
※例えるなら「目的地は千葉県にある東京都千葉市」というような誤ったコーディングとなっています。

このコードを正常に動作するよう修正すると、

Sub 非アクティブシートへの代入()
    Dim 配列()
    Sheet1.Activate
    配列 = Range(Cells(1, 1), Cells(5, 5))
    Range(Sheet2.Cells(1, 1), Sheet2.Cells(5, 5)) = 配列
End Sub

となります。

Rangeの親オブジェクトを省略しているので、OS側に
ActiveSheet.Range(Sheet2.Cells(1, 1), Sheet2.Cells(5, 5)) = 配列
のように認識されてしまいそうな所ですが、
入れ子構造は内部から順に特定されていく(Cellsを特定 → Cellsが入れ子されたRangeを特定の順)ため、問題は生じない模様です。(Sheet2.Cellsの入れ子によって表現されるRange → Sheet2が親オブジェクトだと認識される)

なお、実際のコーディングでは「With構文による親オブジェクト記述の省略」を用いると、「どのシートで何をしているか」が明白になり、可読性が上がりエラー防止にも繋がります。

Sub 別シートへの代入()
    Dim 配列()
    With Sheet1
        配列 = Range(.Cells(1, 1), .Cells(5, 5))
    End With
    With Sheet2
        Range(.Cells(1, 1), .Cells(5, 5)) = 配列
    End With
End Sub

※Sheet1.Activateは代入動作上不要になるので削除しました。

なお、「別のシートへの代入」にはResizeを使ったやり方もあります。

Sub Resizeによる別解()
    Dim 配列()
    With Sheet1
        配列 = Range(.Cells(1, 1), .Cells(5, 5))
    End With
    With Sheet2
        .Cells(1, 1).Resize(5, 5) = 配列
    End With
End Sub

どちらを採用するかはケースバイケースですが、
慣れるとResizeの方が楽かもしれません。