Sheets("Sheet2").Range("B29").Value = Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

I think because you are trying to put into 1 cell the info of several cells.

I don't think that could be possible.


"sam" wrote:

Sheets("Sheet2").Range("B29").Value = Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

I like this kind of syntax:

Dim RngToCopy as range
dim DestCell as range

with worksheets("Sheet1")
set rngtocopy = .range("D8:d38")
end with

set destcell = worksheets("sheet2").range("b29")

destcell.resize(rngtocopy.rows.count,rngtocopy.col umns.count).value _
= rngtocopy.value

or to save typing.

with rngtocopy
destcell.resize(.rows.count,.columns.count).value = .value
end with

(both the sending and receiving range want to be the same size.)

sam wrote:

Sheets("Sheet2").Range("B29").Value = Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

--

Dave Peterson

You can't assign the values from 31 cells (D8:D38) to a single cell (B29).
Maybe this is what you are trying to do...

Sheets("Sheet1").Range("D8:D38").Copy Sheets("Sheet2").Range("B29")

--
Rick (MVP - Excel)


"sam" wrote in message
...
Sheets("Sheet2").Range("B29").Value =
Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

The Issue here is, From that range of cells, Only one cell will have a value.
Hence I want to display that value(which can be in any cell between
Sheet2-D8:D38) into Sheet1-B29

"Rick Rothstein" wrote:

You can't assign the values from 31 cells (D8:D38) to a single cell (B29).
Maybe this is what you are trying to do...

Sheets("Sheet1").Range("D8:D38").Copy Sheets("Sheet2").Range("B29")

--
Rick (MVP - Excel)


"sam" wrote in message
...
Sheets("Sheet2").Range("B29").Value =
Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

I don't think there would have been any way to figure that is what you
wanted from your initial posting. Give this a try...

Sheets("Sheet2").Range("B29").Value = Join(WorksheetFunction.Transpose( _
Sheets("Sheet1").Range("D8:D38")), "")

--
Rick (MVP - Excel)


"sam" wrote in message
...
The Issue here is, From that range of cells, Only one cell will have a
value.
Hence I want to display that value(which can be in any cell between
Sheet2-D8:D38) into Sheet1-B29

"Rick Rothstein" wrote:

You can't assign the values from 31 cells (D8:D38) to a single cell
(B29).
Maybe this is what you are trying to do...

Sheets("Sheet1").Range("D8:D38").Copy Sheets("Sheet2").Range("B29")

--
Rick (MVP - Excel)


"sam" wrote in message
...
Sheets("Sheet2").Range("B29").Value =
Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

Apologies for not making it clear before. This worked out great. Thank you

"Rick Rothstein" wrote:

I don't think there would have been any way to figure that is what you
wanted from your initial posting. Give this a try...

Sheets("Sheet2").Range("B29").Value = Join(WorksheetFunction.Transpose( _
Sheets("Sheet1").Range("D8:D38")), "")

--
Rick (MVP - Excel)


"sam" wrote in message
...
The Issue here is, From that range of cells, Only one cell will have a
value.
Hence I want to display that value(which can be in any cell between
Sheet2-D8:D38) into Sheet1-B29

"Rick Rothstein" wrote:

You can't assign the values from 31 cells (D8:D38) to a single cell
(B29).
Maybe this is what you are trying to do...

Sheets("Sheet1").Range("D8:D38").Copy Sheets("Sheet2").Range("B29")

--
Rick (MVP - Excel)


"sam" wrote in message
...
Sheets("Sheet2").Range("B29").Value =
Sheets("Sheet1").Range("D8:D38").Value

Please Help

Thanks in Advance

On Fri, 28 Aug 2009 09:52:01 -0700, sam
wrote:

Sheets("Sheet2").Range("B29").Value = Sheets("Sheet1").Range("D8:D38").Value


Your source range is 31 cells but your destination range is just one
cell. Only one cell, D8, will be copied.
If you want all 31 cells to be copied you have to have a destination
range with that size.

For example like this:
Sheets("Sheet2").Range("B29:B59").Value =
Sheets("Sheet1").Range("D8:D38").Value


Hope this helps / Lars-Åke