Hi JP

Sub test()
Dim r As Range, i As Long
With Sheets("Sheet1")
Set r = .Range(.Range("A2"), _
..Range("A" & Rows.Count).End(xlUp).Offset(0, 5))
i = r.Rows.Count
End With
With Sheets("Sheet2")
..Range("A2:F" & i).Insert Shift:=xlDown
..Range("A2:F" & i).Formula = r.Value2
End With
End Sub

--
XL2002
Regards

William



"JP" wrote in message
...
| Hi,
|
| I am trying to copy a dynamic range ("Sheet1!A2:F(unknown)") and
| insert the copied content in cell "Sheet2!A2" on top of already
| existing data. I have tried some approaches but haven't been able to
| accomplish this. Recording a macro requires known ranges, I would
| like to accomplish this for variable ranges.
|
| The final approach that I came up with is as follows:
| Range(Sheet1!A2, Sheet1!F65536.End(xlUp)).Value.Copy
| Range(Sheet2!A2).Insert shift:=xlDown
|
| Unfortunately, this doesn't work, and I get the error "object is
| required".
| Does anyone know of a concise solution to this in vba?
|
| Thanks
|
| JP
|