what is wrong with this!?
 

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

Please Help

Thanks in Advance

what is wrong with this!?
 

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

what is wrong with this!?
 

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

what is wrong with this!?
 

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

what is wrong with this!?
 

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

what is wrong with this!?
 

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

what is wrong with this!?
 

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

what is wrong with this!?
 

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