Macro for Copy Paste special Values

[lj]

I'm trying to create a shortcut for using Ctr E S V using a macro.
What I want to do is to be able to copy a cell, go to another cell,
and paste special as values. The code for the macro I'm using is

Sub PasteVal()
Selection.PasteSpecial Paste:=xlValues
End Sub

It errors out when I try and run it though. Any suggestions? Thanks
for your help.

Lisa

 

[muddan madhu]

Selection.PasteSpecial Paste:=xlPasteValues

and also include this Application.CutCopyMode = False

 

[lj]

  Selection.PasteSpecial Paste:=xlPasteValues

and also include this Application.CutCopyMode = False

I tried adding that. The error message I get when I try to execute
the macro is "Paste Special method of range class failed"
This is getting really frustrating. Could there be something wrong
with my excel settings?

 

[muddan madhu]

assumed range A1:A10 you have some data ( values derived from
formula ) and need to paste it in B1:B10

Sub test()
ActiveSheet.Range("A1:A10").Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[lj]

Is it possible to run this macro without having specified what area
you are going to copy or where you are going to copy it to? For
example, I want to copy any cell I want, then select another cell (any
cell I want) and run the copy paste special values macro. Is this
possible?

 

[muddan madhu]

No you can't

Try this

assume u need to copy A1:A10 and paste values in C1:C10

first select the cell or range and then run this macro, it will ask
where to paste just give C1 and ok

Sub test()
Selection.Copy
x = InputBox("select the range")
Range(x).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Dave Peterson]

I think it depends on how you're running the macro.

If I ran the macro via tools|Macro|Macros, then the clipboard was lost and the
code failed.

But if I assigned a shortcut key to the code, then I could use that ok.

I used this to avoid the runtime error:

Option Explicit
Sub testme()

On Error Resume Next
ActiveCell.PasteSpecial Paste:=xlPasteValues
If Err.Number <> 0 Then
MsgBox "Not pasted!"
Err.Clear
End If
On Error GoTo 0

End Sub

The code also worked if I assigned the macro to a button from the Forms toolbar

 

[lj]

I think it depends on how you're running the macro.

If I ran the macro via tools|Macro|Macros, then the clipboard was lost and the
code failed.

But if I assigned a shortcut key to the code, then I could use that ok.

I used this to avoid the runtime error:

Option Explicit
Sub testme()

    On Error Resume Next
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    If Err.Number <> 0 Then
        MsgBox "Not pasted!"
        Err.Clear
    End If
    On Error GoTo 0

End Sub

The code also worked if I assigned the macro to a button from the Forms toolbar

Perfect. This works great. Is there anyway to assign this macro
globally to all of my excel spreadsheets so that I can use it in any
spreadsheet that I open?

 

[Microsoft]

Greetings Lisa. One big caution: Doing pastespecial from a macro clears
undo! You may not want the consequences of that. One workaround is using
sendkeys to send the keystrokes for the final pastespecial action. If you
type the word sendkeys in a module, click in the word, and then right click
on it, you can select "definition" to find out how it works.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[lj]

Oh, that's definitely a problem, I need to be able to undo things.
I'm very interested in this sendkeys. One question I have is do send
keys have to be run from visual basic or can they be run from other
apps as well?

 

[Dave Peterson]

If you need Undo, I think you're out of luck with a macro.

But maybe you can just add an icon to your favorite toolbar (xl2003 and below)
or to the QAT in xl2007:

Tools|customize|Commands tab
Scroll down on the right hand side and drag "paste values" to your favorite
toolbar.

 

[That One Guy]

Here you go:
sub test()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
end sub

One note, most of the time, you cannot paste special from a set of data
with auto-filters (or hidden rows/column) enabled to rows that have an
auto-filter enabled (or hidden rows).

Also, you need to assign a button or shortcut key to your macro.
Otherwise, you lose your selection when going to the execute macro screen.

Thanks.

Regards,

That One Guy.

 

[That One Guy]

1)Create your macro in a blank spreadsheet, and save it as
%userprofile%\application data\microsoft\excel\xlstart\personal.xls.

2)Close and restart Excel.

3)In Excel, hide personals.xls (Window-->Hide).

4)see

for customizing your
toolbar. You'll want to add a new toolbar and a macro button to the
toolbar (or add a macro button to an existing toolbar). Then, simply
assign the button (still in customize toolbar mode) to your macro and
you're all set.

Regards,

That One Guy

 

[That One Guy]

lj wrote:

I'm not sure what you're asking there. Visual Basic for Applications is
the language that the Excel macros are written in. Since VBA is also
used for Word, PowerPoint, and Access, then you could use sendkeys in
them as well, but you would need to write the module for that specific
App (at least in 2003, Word cannot see Excel Macros).

Regards,

That One Guy