Copy Value, but not Formula
 

Try This example:

Sub CopySelection()

Dim rTarget As Range

With Selection
Set rTarget = Selection.Offset(0, 1)
rTarget.Value = Selection.Value
End With

End Sub


Select any number of cells in a column. The code will copy the values of the
selected cells into the adjacent column

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
"Sean" wrote in message
...
A few days ago I posted this message and I got the reply below. It works
great, but I was wondering if there was a way to select the cells first
and
run the macro. For instance, I want to copy the value from Range("f2:f28")
to Range ("e2:e28"). I adjusted the code given to me to look like this...

Range("E2:E28").Value = Range("F2:F28").Value

The end of the range can change from time to time and may be shorter or
longer. That is why I would rather have it only apply the macro to the
selected cells. That way if the range stopped at, say, E24, I could have
it
only change those and not have to change the code every time before I ran
it.
______________________________
Thank you, Sean
Artist/Production Manager
Please visit us at www.oatesflag.com
502-267-8200
502-267-8246 fax

Original msg starts below...
From: Sean
Date Posted: 4/7/2004 3:13:00 PM


I was wondering if it is possible thru VBA to copy the contents of a cell
and paste them to another, but not have the cell change every time the
original cell (which has a formula) changes. For instance if C5 has a
formula: =(R5-S5)*2

I want to be able to copy the resulting value of C5 to, say, B5. But I
only
want the value, not the formula. So that B5 doesn't change every time C5,
R5,
or S5 does. I want to be able to run this macro anytime I need to make
B5=C5.

--
______________________________
Thank you, Sean
Artist/Production Manager
Please visit us at www.oatesflag.com
502-267-8200
502-267-8246 fax




Copy Value but not Formula
From: Ron de Bruin
Date Posted: 4/7/2004 3:13:00 PM



You can use this to copy only the value

Range("B5").Value = Range("C5").Value


--
Regards Ron de Bruin
http://www.rondebruin.nl

Copy Value, but not Formula
 

Offset should be -1 (from column F to column E).

Sub CopySelection()
Selection.Copy
Selection.Offset(0, -1).PasteSpecial xlPasteValues
End Sub


"Patrick Molloy" wrote in message
...
Try This example:

Sub CopySelection()

Dim rTarget As Range

With Selection
Set rTarget = Selection.Offset(0, 1)
rTarget.Value = Selection.Value
End With

End Sub


Select any number of cells in a column. The code will copy the values of
the
selected cells into the adjacent column

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
"Sean" wrote in message
...
A few days ago I posted this message and I got the reply below. It works
great, but I was wondering if there was a way to select the cells first
and
run the macro. For instance, I want to copy the value from
Range("f2:f28")
to Range ("e2:e28"). I adjusted the code given to me to look like
this...

Range("E2:E28").Value = Range("F2:F28").Value

The end of the range can change from time to time and may be shorter or
longer. That is why I would rather have it only apply the macro to the
selected cells. That way if the range stopped at, say, E24, I could have
it
only change those and not have to change the code every time before I
ran
it.
______________________________
Thank you, Sean
Artist/Production Manager
Please visit us at www.oatesflag.com
502-267-8200
502-267-8246 fax

Original msg starts below...
From: Sean
Date Posted: 4/7/2004 3:13:00 PM


I was wondering if it is possible thru VBA to copy the contents of a
cell
and paste them to another, but not have the cell change every time the
original cell (which has a formula) changes. For instance if C5 has a
formula: =(R5-S5)*2

I want to be able to copy the resulting value of C5 to, say, B5. But I
only
want the value, not the formula. So that B5 doesn't change every time
C5,
R5,
or S5 does. I want to be able to run this macro anytime I need to make
B5=C5.

--
______________________________
Thank you, Sean
Artist/Production Manager
Please visit us at www.oatesflag.com
502-267-8200
502-267-8246 fax




Copy Value but not Formula
From: Ron de Bruin
Date Posted: 4/7/2004 3:13:00 PM



You can use this to copy only the value

Range("B5").Value = Range("C5").Value


--
Regards Ron de Bruin
http://www.rondebruin.nl