copy variable length cells to a new sheets

[Guest]

I would like to copy a range of cells in to a new spreadsheet.

I have following code, but it does not work for variable length cells

Dim wsSource wsDestination As Worksheet
Dim rFrom, rTo As Range

Set wsSource = Sheets("SHEET1")
Set wsDestination = Sheets("SHEET2")
Set rOld = wsSource.Range("A1:C15")
Set rNew = wsTarget.Range("A1")

rOld.Copy Destination:=rNew

Number of ciolumns is known, but I would like to change the number of rows.
Are there any easy way to programming change row number of rOld?

Any information is great appreciated,

 

[Norman Jones]

Hi Souris,

Set rOld = wsSource.Range("A1:C15")

Perhaps,

Set rOld = Range("A1").CurrentRegion

 

[Guest]

Thanks for the information,

CurrentRegion copies all the region, but I want to copy part of them.

For example: I wanted to copy "A1:C15", My data are in "A1:F15".
CurrentRegion copy from A1:F15, which I do not need "D1:F15"

May I specify the number of columns or I can delete the row I do not need
after copy?

Thanks again,

 

[Norman Jones]

Hi Souris,

Try:

Range("A1").CurrentRegion.columns("A:C").select

 

[Guest]

run-time error 424 object required.

Hi Souris,

Try:

Range("A1").CurrentRegion.columns("A:C").select

 

[Guest]

Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rOld As Range
Dim rNew As Range
Dim eRow As Long

Set wsSource = Sheets("SHEET1")
Set wsTarget = Sheets("SHEET2")
eRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
Set rOld = wsSource.Range("A1:C" & eRow)
Set rNew = wsTarget.Range("A1")

rOld.Copy Destination:=rNew

PS if you dim your variables:

Dim wsOne, wsTwo as Worksheet

wsOne will be a variant and only wsTwo will be correctly dimmed.

Hope this helps
Rowan