Paste Value Macro

[AA]

Here's a macro that works fine for me. It converts a formula in the
cell where the cursor is to a value, or formulas in selected cells to
values:


Sub ConvertToValue()
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

What I want is a macro that, after I copy a selection to the
clipboard, will allow me to PasteSpecial>ValuesOnly to the new
location.

I tried:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

but that didn't work. I added ActiveCell.Select to the beginning, but
that didn't work either.

What am I missing?

--------------------

Once I do that, is there a way to add it to the right-click menu, like
one can do so easily in Word?


TIA,

Andy

 

[Pete JM]

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False

The SkipBlanks:=_ was the wrong way round on your

 

[Don Guillett]

You don't need to select
to copy
range("a1").copy range("b4")
or for values only
range("b4:b10").value = range("a4:a10").value

 

[AA]

You don't need to select to copy

range("a1").copy range("b4")
or for values only
range("b4:b10").value = range("a4:a10").value

I did the first macro by recording it, then thought I could edit it do
the second one, since recording it didn't work.

Here's what I want to do -

I want to manually select a range of cells, manually go to a different
location, perhaps on a different sheet, and then run a macro to Paste
only the Value to the new location (not the format or any formulas).

 

[Don Guillett]

If you record a macro while selecting a cell>copy>moving to another
sheet>paste special, you get
Sub Macro1()

' Selection.Copy
' Sheets("Sheet6").Select
' Range("G6").Select
Selection.PasteSpecial Paste:=xlPasteValues', Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
==
I commented out the part about selecting & copying. So, do your selecting &
moving and then use this.
OR, If not on your toolbar put the copy and paste special values (looks like
a box with a 12 on it) and use that.

 

[AA]

Selection.PasteSpecial Paste:=xlPasteValues', Operation:=xlNone,

SkipBlanks _
:=False, Transpose:=False

Don,

Arrggh! That worked, and I had gotten there before, and it didn't
work. But it did and didn't.....

Turns out that it works fine if run from a Shortcut Key or Toolbar
Button. But if you try to run it from Alt-F8, or Tools>Macro>Macros,
it doesn't work because that causes the Excel internal clipboard to
empty itself, so there's nothing to paste.

So I'm all set (for now).

Thanks!

Andy

 

[Don Guillett]

You're right. Won't work from altf8. Try altf11 or assign to a shape or add
the paste special icon to your toolbar as I suggested. Right click
toolbar>customize>commands>edit>look for the icon that looks like a 12 on a
box>drag to toolbar.

I just re-tested.
1. Selected a cell
2. Copied that cell
3. Selected another cell
4. Executed the macro from the vbe.

 

[AA]

assign to a shape or add

the paste special icon to your toolbar as I suggested.

I assigned the macro PasteVal() to a hotkey, works fine like that.

I also put it on my right click menu, also works fine:

Sub CreateRightClick()
With Application.CommandBars("Cell").Controls.Add
.Caption = "Paste Values"
.OnAction = "PasteVal"
End With
End Sub

I was only invoking it from Alt-F8 to troubleshoot it, which was what
caused the trouble!