Copy Paste Special Macro

B

Bud Hughes

I need to copy the value of a formula into a cell one down and one to the
left of the current cell I am copying. Rather then right click and select
the options. I can build a macro to copy then paste special but...I want the
copy to be the current cell I am in then move the active cell on down and one
left then paste special. Is there a way to use visual basic to move one cell
left and one cell down and move only the information in the cell?
 
G

Gary's Student

The following sub will do what you want:

Sub DownAndLeft()
Dim x As Variant
x = Selection.Value
Selection.Offset(1, -1) = x
End Sub

Select the cell with the formula and run the macro.

Good Luck
 
D

Dave Peterson

Another option (manual, though):

If you rightclick on that cell's border, you can drag it to where you want it
(down and to the left) and when you let go, you'll be presented with a list of
options.

One of them is "copy here as values only"

(sometimes, it's quicker than finding the macro to run!)

===
Another option...

Add a "Paste Values" icon to your favorite toolbar.
(tools|Customize|commands tab|edit category, look for "paste values")

Then you can hit the copy icon (or ctrl-c), move the cursor, and hit that "paste
values" icon.
 
Top