Paste special shortcut

[Klemen25]

Hello
I read posts concerning paste special shortcut, but I do not know how
to do this:
I would like to assign a shortcut (lets say ctrl + m) to opening of
paste special dialog box.

I tried this macro:

Sub Pastespecial()
Application.Dialogs(xlDialogPasteSpecial).Show
End Sub

and to assign shortcut to it later, but again my lack of basics in
visual basic is kicking my balls...
So any ideas how to correctly tell Excel to open this dialog box?

Thank you!

 

[Roger Govier]

Hi

You don't say what problem you are getting, or whether it is wanting to know
how to assign Control+m to the macro.

As it stands, your macro would give you unexpected results, if you have not
Copied or Cut anything before wanting to paste Special.
Try amending it to

Sub Pastespecial()
If Application.CutCopyMode > 0 Then
Application.Dialogs(xlDialogPasteSpecial).Show
Else
MsgBox "Nothing has been selected to Paste"
End If
Application.CutCopyMode = False
End Sub

To assign the shortcut, Alt+F8 to bring up the Macros list>highlight
PasteSpecial>Options>enter m in the box>OK

 

[Klemen25]

Thank you.
Sorry for not beeing clearer- I needed help with the macro only...

But this macro does not work.
Sub Pastespecial()
If Application.CutCopyMode > 0 Then
Application.Dialogs(xlDialogPasteSpecial).Show
Else
MsgBox "Nothing has been selected to Paste"
End If
Application.CutCopyMode = False
End Sub

If I copy one cell select another in which I want to paste special
and run this macro it also reports that nothing was selected.

 

[Roger Govier]

Hi

Works perfectly for me.
How are you copying? Using right click>Copy or Control+C both work OK for
me.
Are you pressing Escape after making the copy, if so you will have emptied
the clipboard.
You should see the "marching ants" around the cell(s) you have copied.
Where are you placing the macro?
It should be in a module that has been inserted in a workbook.

Alt+F11 to invoke VB Editor
Insert>Module
Paste the code
Alt+F11 to return to Excel.

 

[Klemen25]

I use ctrl c.
I see the ants- I then click to another cell and run the macro, (I do
not press esc), but then I get the message that nothing was selected.
I placed the macro both in personal xls, and in the workbook I was
testing in- it still doesnt work.

Also if I only run this macro I get the error "show method of Dialog
class failed"

Sub Pastespecial()
Application.Dialogs(xlDialogPasteSpecial).Show
End Sub

 

[Roger Govier]

Hi

Send me the file direct and I will take a look
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address