Paste special shortcut

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Klemen25 - 16 May 2008 07:54 GMT

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!

Reply to this Message

Roger Govier - 16 May 2008 10:33 GMT

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

Signature

Regards
Roger Govier

> Hello
> I read posts concerning paste special shortcut, but I do not know how
[quoted text clipped - 13 lines]
>
> Thank you!

Reply to this Message

Klemen25 - 16 May 2008 11:31 GMT

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.

Reply to this Message

Roger Govier - 16 May 2008 12:01 GMT

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.

Signature

Regards
Roger Govier

> Thank you.
> Sorry for not beeing clearer- I needed help with the macro only...
[quoted text clipped - 11 lines]
> 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.

Reply to this Message

Klemen25 - 16 May 2008 12:17 GMT

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

Reply to this Message

Roger Govier - 16 May 2008 12:24 GMT

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

Signature

Regards
Roger Govier

> I use ctrl c.
> I see the ants- I then click to another cell and run the macro, (I do
[quoted text clipped - 8 lines]
> Application.Dialogs(xlDialogPasteSpecial).Show
> End Sub

Reply to this Message