Hi,

I try to copy a value to a different sheet in the
workbook through the below line

Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")

B5 contains the product number(this is some formula). In
A1 i get "#Ref!" instead of the product number. what do i
do to solve this..
Any help would be appreciated.

Thanks,
Neeraja.

Neeraja -

I try to copy a value to a different sheet in the workbook through the
below line
Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")
B5 contains the product number(this is some formula). In A1 i get "#Ref!"
instead of the product number. what do i do to solve this..

One way is to first Copy and then separately PasteSpecial with the
xlPasteValues option.

- Mike Middleton, www.usfca.edu/~middleton

"Michael R Middleton" wrote in message
...
Neeraja -

I try to copy a value to a different sheet in the workbook through the
below line
Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")
B5 contains the product number(this is some formula). In A1 i get
"#Ref!"
instead of the product number. what do i do to solve this..

One way is to first Copy and then separately PasteSpecial with the
xlPasteValues option.

- Mike Middleton, www.usfca.edu/~middleton



Out of interest, why does it not work if you insert the value property in
the above?

Worksheets(Esheet1).Cells("B5").Value.Copy etc

If you look at VBA's help for .copy, you'll notice that it applies to
objects--not simple variables.

dim myVal as string
myval.copy
fails. But if you copy a cell, it works ok.

Another way to "copy" the value from one cell to another is to just plop it in
the

worksheets("dummydata").range("A1").value _
= worksheets("esheet1").range("b5").value

(watch out for that cells("b5") stuff, too. Either range("B5") or cells(5,"B")
or even cells(5,2) would be ok.)



GB wrote:

"Michael R Middleton" wrote in message
...
Neeraja -

I try to copy a value to a different sheet in the workbook through the
below line
Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")
B5 contains the product number(this is some formula). In A1 i get
"#Ref!"
instead of the product number. what do i do to solve this..

One way is to first Copy and then separately PasteSpecial with the
xlPasteValues option.

- Mike Middleton, www.usfca.edu/~middleton



Out of interest, why does it not work if you insert the value property in
the above?

Worksheets(Esheet1).Cells("B5").Value.Copy etc

--

Dave Peterson

"Dave Peterson" wrote in message
...
If you look at VBA's help for .copy, you'll notice that it applies to
objects--not simple variables.

Ah! Too subtle for me.


dim myVal as string
myval.copy
fails. But if you copy a cell, it works ok.

Another way to "copy" the value from one cell to another is to just plop
it in
the

worksheets("dummydata").range("A1").value _
= worksheets("esheet1").range("b5").value

Ah! Just what I wanted, but was too close to the trees to see. I kept using
copy and pastespecial, which is clearly inferior.

Incidentally, this works too
Range("E3")= Range("D3")
Is this a case of Excel assuming (helpfully) that you mean the value
property when nothing is inserted?


Incidentally, the following works

Range("E5").Font.Bold= Range("E3").Font.Bold

I can see the logic of this. You are setting the Bold property of E5's font
object equal to the value of E3's. But how do you set all of the font
properties at the same time? I thought I could use Set to make one object
equal another, so what is wrong with:

set Range("E5").font= range("E3").font

I hope I'm not being too tiresome!


(watch out for that cells("b5") stuff, too. Either range("B5") or
cells(5,"B")
or even cells(5,2) would be ok.)

Noted, thanks.

Thanks very much

Geoff






GB wrote:

"Michael R Middleton" wrote in message
...
Neeraja -

I try to copy a value to a different sheet in the workbook through
the
below line
Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")
B5 contains the product number(this is some formula). In A1 i get
"#Ref!"
instead of the product number. what do i do to solve this..

One way is to first Copy and then separately PasteSpecial with the
xlPasteValues option.

- Mike Middleton, www.usfca.edu/~middleton



Out of interest, why does it not work if you insert the value property
in
the above?

Worksheets(Esheet1).Cells("B5").Value.Copy etc

--

Dave Peterson

GB wrote:

<<snipped

Incidentally, this works too
Range("E3")= Range("D3")
Is this a case of Excel assuming (helpfully) that you mean the value
property when nothing is inserted?

Yep. The default property for a range object is .value.


Incidentally, the following works

Range("E5").Font.Bold= Range("E3").Font.Bold

I can see the logic of this. You are setting the Bold property of E5's font
object equal to the value of E3's. But how do you set all of the font
properties at the same time? I thought I could use Set to make one object
equal another, so what is wrong with:

set Range("E5").font= range("E3").font

You use Set with objects and Let (or nothing) with non-objects--simple variable
type thingys.

<<snipped

--

Dave Peterson