Paste Value Macro

  • Thread starter Thread starter AA
  • Start date Start date
A

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
 
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False

The SkipBlanks:=_ was the wrong way round on your
 
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
 
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
Click to expand...

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 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.
 
Selection.PasteSpecial Paste:=xlPasteValues', Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Click to expand...

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
 
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.
 
assign to a shape or add
the paste special icon to your toolbar as I suggested.
Click to expand...

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!
 
Back
Top