| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
| Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
| Find a range of values in a range of cells | Excel Worksheet Functions | |||
| Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
| Searching a range and copying values into a new range.... **Please help** :( | Excel Programming | |||
Linear Mode
