Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying and inserting lines | Excel Worksheet Functions | |||
Copying row & inserting | Excel Discussion (Misc queries) | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Inserting Lines And Copying Them | Excel Programming |