Copy value

  • Thread starter Thread starter Artur Pomianowski
  • Start date Start date
A

Artur Pomianowski

I want to use shortcut key to copy value in spreadsheet.

The VBA code is:

Sub Paste_Value()
' shortcut key: Ctrl+q
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues
End Sub

But if I don't copy cell and I use shortcut key, the macro doesn't work.
How can I modify this macro?

Artur
 
Hi Artur!

Try:

Sub Paste_Value()
' shortcut key: Ctrl+q
ActiveCell.Copy
Selection.PasteSpecial Paste:=xlValues
End Sub

You need to copy before you paste special.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I know, but for example If I forget to copy, I don't want to see message
with VBA error.
 
Hi Artur!

I think you need to explain precisely what you want your macro to do.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Artur!

I think you need to explain precisely what you want your macro to do.
Click to expand...

Hi Norman,

Sub Paste_Value()
' shortcut key: Ctrl+q
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues
End Sub

This macro works when I copy before I use shortcuyt key.
But when I don't copy before (by mistake) and I use macro I see message with
VBA error.
I want to modify code to exclude this VBA error.

Artur
 
If you are trying to turn the active cell formula into a value just use

sub makevalue()
activecell.formula=activecell.value
end sub
or
activecell.value=activecell.value
 
One way to avoid the error is to check to see if you copied before hand:

Option Explicit

Sub Paste_Value()
' shortcut key: Ctrl+q
If Application.CutCopyMode <> xlCopy Then
MsgBox "don't forget to copy first"
Else
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues
End If

End Sub

And did you know that you can customize your favorite toolbar and drag a Paste
Values icon to it. It might not be as nice as the short cut key,

Tools|customize|Commands Tab
Edit category|
drag "Paste Values" to your favorite toolbar.

One good thing about this is you don't lose your Edit|Undo capability.
 
Back
Top