Sleeper: Copy dynamic range from one sheet to another

[mohan]

i'm having a very tiresoem problem at the momment.

I want to copy the range from column "p" from "sheet1"

to cell "d8" in "sheet2"

the thing is though that the number of rows in "p" can change, ie

there can by 5 rows of data, or 100 rows of data

this is what i've come up with, but it only takes the last cell and copies it?...

many thanks in advance.


Private Sub CommandButton5_Click()
Dim frows As Double
Dim i As Double
Worksheets("sheet1").Select
frows = Cells(rows.Count, 5).End(xlUp).Row
Range("e" & frows).Select
Selection.Copy
Worksheets("sheet2").Select
For i = 1 To frows
Cells(i, 10).Select
ActiveSheet.Paste
Next i
End Sub

[Paul_Hossler]

One way



Sub Macro1()
With Worksheets("Sheet1")
Range(.Cells(1, 16), .Cells(.Rows.Count, 16).End(xlUp)).Copy (Worksheets("sheet2").Range("D8"))
End With
End Sub


Paul

[Bob Phillips]

More qualification required



Sub Macro1()
With Worksheets("Sheet1")
.Range(.Cells(1, "E"), .Cells(.Rows.Count, "E").End(xlUp)).Copy Worksheets("sheet2").Range("D8")
End With
End Sub

[Paul_Hossler]

Nit, it was Col P

But I assume you're refering to .Range instead of "no dot" Range.

It seems to work reliably without the dot, since 'Range' returns an object based on it's parameters, and the.Cells () are exactly defined by the 'With'.

Are there some circumstances where that would be different (he asked in order to learn :dunno )? I've always not qualified Range if the parameters are definately defined.

Paul

[Bob Phillips]

It only works if the activesheet is Sheet1, otherwise Range refers to the activesheet, which could be a chart sheet.

[mohan]

it worked like a charm

brilliant, many thanks

[Paul_Hossler]

:dunno but as far as I can tell in 2003 and 2007, the "no dot' range works as expected regardless of the currently active sheet, worksheet or chart.

I still think that the .Cells in the Range(...) make it independent of the active sheet, but it is something to be aware of

Paul



Sub Macro1()
Sheets("chart1").Select
' Worksheets("sheet3").select
With Worksheets("Sheet1")
Range(.Cells(1, 16), .Cells(.Rows.Count, 16).End(xlUp)).Copy (Worksheets("sheet2").Range("D8"))
End With
End Sub