Pasting Values of range to another range
 

Thanks in advance for any help

Usually I like to use something like

Paste multi-cell range to a single cell (similar to paste All)
Sheets(1).Rng1.copy _
Detination:Sheets(2).Range("A1")

or

Paste multi-cell range to an identically sized range (similar to paste
value)
Sheets(2).Rng2.Value = Sheets(1).Rng1.Value

Is there a way to amend the first code into a paste value version, without
defining the full paste-to range?

Thanks...

--
steveB

Remove "AYN" from email to respond

Pasting Values of range to another range
 

Hi Steve

See how I use it here
http://www.rondebruin.nl/copy1.htm

Sub copy_1_Values_ValueProperty()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("A1:c10")
With sourceRange
Set destrange = Sheets("Sheet2").Range("A" & Lr). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"STEVE BELL" wrote in message news:%Zz2f.5422$nz.3648@trnddc03...
Thanks in advance for any help

Usually I like to use something like

Paste multi-cell range to a single cell (similar to paste All)
Sheets(1).Rng1.copy _
Detination:Sheets(2).Range("A1")

or

Paste multi-cell range to an identically sized range (similar to paste value)
Sheets(2).Rng2.Value = Sheets(1).Rng1.Value

Is there a way to amend the first code into a paste value version, without defining the full paste-to range?

Thanks...

--
steveB

Remove "AYN" from email to respond

Pasting Values of range to another range
 

Ron,

Thanks for the reply,

But guess I didn't say it right.

You are doing what I wanted to avoid - that is defining the full range to
paste to.

My first example is able to copy a range and paste it to a single cell. And
the pasted range will self-expand to a range = to the same size as the
first.

In my second example both ranges need to be fully defined.

All I want to do is paste values of a defined range to a single cell and
have it self-extend.
And do all of this without selecting anything.

I have to admit that using resize gets close to this...

sheets(2).Range("A1").Resize(#rows,#cols).value=so urcerange.value

--
steveB

Remove "AYN" from email to respond
"Ron de Bruin" wrote in message
...
Hi Steve

See how I use it here
http://www.rondebruin.nl/copy1.htm

Sub copy_1_Values_ValueProperty()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("A1:c10")
With sourceRange
Set destrange = Sheets("Sheet2").Range("A" & Lr). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"STEVE BELL" wrote in message
news:%Zz2f.5422$nz.3648@trnddc03...
Thanks in advance for any help

Usually I like to use something like

Paste multi-cell range to a single cell (similar to paste All)
Sheets(1).Rng1.copy _
Detination:Sheets(2).Range("A1")

or

Paste multi-cell range to an identically sized range (similar to paste
value)
Sheets(2).Rng2.Value = Sheets(1).Rng1.Value

Is there a way to amend the first code into a paste value version,
without defining the full paste-to range?

Thanks...

--
steveB

Remove "AYN" from email to respond

Pasting Values of range to another range
 

Read good

It resize the destrange to the same size as the sourceRange

Try this example

Set sourceRange = Sheets("Sheet1").Range("A1:c10")
With sourceRange
Set destrange = Sheets("Sheet2").Range("A1"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"STEVE BELL" wrote in message news:zuA2f.5425$nz.1837@trnddc03...
Ron,

Thanks for the reply,

But guess I didn't say it right.

You are doing what I wanted to avoid - that is defining the full range to paste to.

My first example is able to copy a range and paste it to a single cell. And the pasted range will self-expand to a range = to the
same size as the first.

In my second example both ranges need to be fully defined.

All I want to do is paste values of a defined range to a single cell and have it self-extend.
And do all of this without selecting anything.

I have to admit that using resize gets close to this...

sheets(2).Range("A1").Resize(#rows,#cols).value=so urcerange.value

--
steveB

Remove "AYN" from email to respond
"Ron de Bruin" wrote in message ...
Hi Steve

See how I use it here
http://www.rondebruin.nl/copy1.htm

Sub copy_1_Values_ValueProperty()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("A1:c10")
With sourceRange
Set destrange = Sheets("Sheet2").Range("A" & Lr). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"STEVE BELL" wrote in message news:%Zz2f.5422$nz.3648@trnddc03...
Thanks in advance for any help

Usually I like to use something like

Paste multi-cell range to a single cell (similar to paste All)
Sheets(1).Rng1.copy _
Detination:Sheets(2).Range("A1")

or

Paste multi-cell range to an identically sized range (similar to paste value)
Sheets(2).Rng2.Value = Sheets(1).Rng1.Value

Is there a way to amend the first code into a paste value version, without defining the full paste-to range?

Thanks...

--
steveB

Remove "AYN" from email to respond

Pasting Values of range to another range
 

Thanks Ron!

Resize is the way to go....

--
steveB

Remove "AYN" from email to respond
"Ron de Bruin" wrote in message
...
Read good

It resize the destrange to the same size as the sourceRange

Try this example

Set sourceRange = Sheets("Sheet1").Range("A1:c10")
With sourceRange
Set destrange = Sheets("Sheet2").Range("A1"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"STEVE BELL" wrote in message
news:zuA2f.5425$nz.1837@trnddc03...
Ron,

Thanks for the reply,

But guess I didn't say it right.

You are doing what I wanted to avoid - that is defining the full range to
paste to.

My first example is able to copy a range and paste it to a single cell.
And the pasted range will self-expand to a range = to the same size as
the first.

In my second example both ranges need to be fully defined.

All I want to do is paste values of a defined range to a single cell and
have it self-extend.
And do all of this without selecting anything.

I have to admit that using resize gets close to this...

sheets(2).Range("A1").Resize(#rows,#cols).value=so urcerange.value

--
steveB

Remove "AYN" from email to respond
"Ron de Bruin" wrote in message
...
Hi Steve

See how I use it here
http://www.rondebruin.nl/copy1.htm

Sub copy_1_Values_ValueProperty()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("A1:c10")
With sourceRange
Set destrange = Sheets("Sheet2").Range("A" & Lr). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"STEVE BELL" wrote in message
news:%Zz2f.5422$nz.3648@trnddc03...
Thanks in advance for any help

Usually I like to use something like

Paste multi-cell range to a single cell (similar to paste All)
Sheets(1).Rng1.copy _
Detination:Sheets(2).Range("A1")

or

Paste multi-cell range to an identically sized range (similar to paste
value)
Sheets(2).Rng2.Value = Sheets(1).Rng1.Value

Is there a way to amend the first code into a paste value version,
without defining the full paste-to range?

Thanks...

--
steveB

Remove "AYN" from email to respond

Pasting Values of range to another range
 

without
defining the full paste-to range?

No.

Plus assigning a value isn't really pasting (at least I wouldn't call it
such - for what that is worth <g)

--
Regards,
Tom Ogilvy

"STEVE BELL" wrote in message
news:%Zz2f.5422$nz.3648@trnddc03...
Thanks in advance for any help

Usually I like to use something like

Paste multi-cell range to a single cell (similar to paste All)
Sheets(1).Rng1.copy _
Detination:Sheets(2).Range("A1")

or

Paste multi-cell range to an identically sized range (similar to paste
value)
Sheets(2).Rng2.Value = Sheets(1).Rng1.Value

Is there a way to amend the first code into a paste value version, without
defining the full paste-to range?

Thanks...

--
steveB

Remove "AYN" from email to respond