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,

Hi Souris,

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

Perhaps,

Set rOld = Range("A1").CurrentRegion


---
Regards,
Norman



"Souris" wrote in message
...
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,

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" wrote:

Hi Souris,

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

Perhaps,

Set rOld = Range("A1").CurrentRegion


---
Regards,
Norman



"Souris" wrote in message
...
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,

Hi Souris,

Try:

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


---
Regards,
Norman



"Souris" wrote in message
...
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" wrote:

Hi Souris,

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

Perhaps,

Set rOld = Range("A1").CurrentRegion


---
Regards,
Norman



"Souris" wrote in message
...
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,

run-time error 424 object required.



"Norman Jones" wrote:

Hi Souris,

Try:

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


---
Regards,
Norman



"Souris" wrote in message
...
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" wrote:

Hi Souris,

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

Perhaps,

Set rOld = Range("A1").CurrentRegion


---
Regards,
Norman



"Souris" wrote in message
...
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,

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

"Souris" wrote:

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,