Insert Copied Cells as References into a Named Ran

Question

I have a named range of cells in 'Sheet1' lets call them 'Range1'. I have another range of cells in 'Sheet2' lets call them 'Range2'.

I want to copy the references from Range1 and paste them into Range2 but at the start or end of the range, so Range2 will automatically extend to incorporate these new cells.

I have already tried the following code but it doesn't work.

Sheets("Sheet1").Range("Range1")
Sheets("Sheet2").Range("Range2")(1).Select
Selection.Insert Shift:=xlDown Link:=True

I am new to VBA so I am aware the above may be completely the wrong way of going about it! Alternative solutions would be appreciated as well.

Answer 1

I managed to solve this using the following code:

Sheets("Sheet2").Range("Range2").Offset(1, 0).Insert _ 
   CopyOrigin:=xlFormatFromRightOrBelow
For X = 1 To 30
    For Y = 1 To 4
        Sheets("Sheet2").Range("Range2").Cells(X, Y) = "=" &  _ 
           Sheets("Sheet1").Range("Range1").Cells(X, Y).Address(False, False, , True)
    Next
Next