VBA: copying and inserting a dynamic range
 

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

copying and inserting a dynamic range
 

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
|

copying and inserting a dynamic range_Attn: William
 

William,

Thanks so much for the reply. The code worked alright except for a
minor snag (inserting an extra blank line, which I managed to work
around it).

Have a great summer

JP


On Tue, 1 Jun 2004 03:55:43 +0100, "William"
wrote:

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