「Sheet2」のA1セルに「=Sheet1!A1」と式をセットして、そのセルを縦横の範囲に貼り付ければ参照できます。
ここはマクロの説明なので、計算式の説明はしませんが、$A$1:$D$3の範囲に1行のマクロで式をセットします。記録に頼らず、直接記述してみます。
Option Explicit
Sub TEST1()
Worksheets("Sheet2").Range("$A$1:$D$3").FormulaR1C1 = "=Sheet1!RC"
End Sub
Range("$A$1:$D$3").FormulaR1C1 = "=Sheet1!RC"
これが肝心部分です。FormulaR1C1プロパティは、R1C1参照形式で式をセットします。FormulaR1C1プロパティを右辺に書けば式の参照になります。A1参照形式で式をセットする場合は、Formulaプロパティを使います。
なぜ、R1C1参照形式を使うかは、「Sheet2」を見ていただくと解ります。シートの方はA1参照形式で表示されていますから、A1セルの式は「=Sheet1!A1」です。右に移動すると「=Sheet1!B1」になります。
このようにA1参照形式では各セルの式(の文字列)自体が異なるため、マクロで計算式をセットするのに1セルずつ編集しなければなりません。
そこでR1C1参照形式の登場です。ツールメニューのオプション、全般タブで「R1C1参照形式を使用する」にチェックを付けると、
このように、どのセルにも全く同じ「=Sheet1!RC」という式が入ります。
「RC」は「R」にも「C」にも数字を付けていないので、「縦も横も相対参照」ということを意味します。つまり「Sheet1」の同じセルアドレスを参照しなさいという式なのです。
この前にある「Worksheets("Sheet2")」は、この計算式を貼り付けるシートを明示する記述です。
「Worksheets("Sheet2")」を書かずに 「Range("$A$1:$D$3").FormulaR1C1 = "=Sheet1!RC"」だけでもマクロは動作しますが、作用するシートが明示されていないとアクティブなシートに式がセットされます。
「Worksheets("Sheet2")」を前に書いておくと、「Sheet1」がアクティブになっている状態でマクロを動かしても
「Sheet2」の方に式がセットされます。
では、これだけでは面白くないでしょうから、サンプルをもう一つ。
「Sheet2」には見出しを付けることとし、見出しの行の分を1行空けて2行目から下に貼り付けます。
Option Explicit
Sub TEST1()
Worksheets("Sheet2").Range("$A$2:$D$4").FormulaR1C1 = "=Sheet1!R[-1]C"
End Sub