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