Paste Value Macro

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 PasteSpecialValuesOnly 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

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

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


---
Message posted from http://www.ExcelForum.com/

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

--
Don Guillett
SalesAid Software

"AA" wrote in message
...
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 PasteSpecialValuesOnly 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

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).

If you record a macro while selecting a cellcopymoving to another
sheetpaste 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.
--
Don Guillett
SalesAid Software

"AA" wrote in message
...
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).

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 ToolsMacroMacros,
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

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
toolbarcustomizecommandseditlook for the icon that looks like a 12 on a
boxdrag 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.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
If you record a macro while selecting a cellcopymoving to another
sheetpaste 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.
--
Don Guillett
SalesAid Software

"AA" wrote in message
...
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).

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!