Insert Copied Cells as References into a Named Range

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.

It isn't very clear what you are trying to do. Can you provide a clearer example? — Dave
@Dave I basically want to copy a range of cells, and insert them into the middle of another named range of cells. Preferably just after the first row or just before the last row. Does that make sense? — Jonny Pringle
So you want each cell in Range2 to contain a formula equating it to the corresponding cell in Range1? e.g. Range2(1,1).Formula = "=" & Range1(1,1).Address — CodeJockey
No I want to add Range1 to the middle of Range2 so the existing cells get shifted down. The end result should be Range2 with new cells from Range1. — Jonny Pringle
I think you should get what you want from Sheet1.Range("Range1").copy then Sheet2.Range(yourRef).Insert Shift:=xlDown. Is there any reason you are trying to paste this in the middle of the data though? If it is to automatically resize the range, you could paste Range1 at the end of Range2 and use the Range.Resize function or you could use a dynamic range (see ozgrid.com/Excel/DynamicRanges.htm) which will extend automatically. — Dave

Jonny Pringle Jonny Pringle · Accepted Answer · 2014-09-19T14:47:57

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

Insert Copied Cells as References into a Named Range

1 Answers