24 thoughts on “Mouse shortcuts”

1. I’m a bit in between really… I use the mouse a lot (I’ve played a lot of Quake in my time), but I use the keyboard when it’s quicker to do so.

   I have Paste Special Formats and Paste Special Values as toolbar buttons. That works great of me. I’m starting to need Paste Special Formula too, but I’m too lazy to configure the button just now.

   Some time ago (when I was participating in NG), I came across a keyboard shortcut for paste-special values.
   Alt e s v enter
   (I had to fire up Excel and validate the sequence, my fingers know it better than I do)

   It took a little while to get the hang of, since the keys are in awkward positions, but now I perform in a flash. I hardly ever use the toolbar buttons for Paste Special. Whats more, it’s available on any Excel I use (I work in an IT dept, so I remote control other ppl’s computer often)

2. I discovered Juan’s drag method by accident awhile back.

   I too, have the paste values button on one of my toolbars.

3. this right-button trick is great ! I’m interested to know how you discovered it !?

   I am a keyboard-addict myself. I used to use the “Alt E S V” (Values) “Alt E S T” (Formats) and “Alt E S F” (Formulas). but then I read a tip somewhere about the macros below that can reduce the number of keystrokes. In addition, once you assign a keyboard shortcut for each macro, the process becomes even simpler. On my keyboard it’s the Shift+Ctrl+V for Paste Values, Shift+Ctrl+T for Formats, Shift+Ctrl+F for Formulas, etc…

   here are the macros :
   Sub PasteFormulas()
   On Error Resume Next
   Selection.PasteSpecial Paste:=xlFormulas
   End Sub

   Sub PasteValues()
   On Error Resume Next
   Selection.PasteSpecial Paste:=xlValues
   End Sub

   Sub PasteComments()
   On Error Resume Next
   Selection.PasteSpecial Paste:=xlComments
   End Sub

   Sub PasteFormats()
   On Error Resume Next
   Selection.PasteSpecial Paste:=xlFormats
   End Sub

   and the Declaration in the Workbook_Open of the Add-in :
   Application.MacroOptions _
   Macro:=”PasteComments”, _
   Description:=”Paste Special –> Comments”, _
   Hasshortcutkey:=True, _
   ShortcutKey:=”M”

4. I customized my menus when I got tired of Alt-e-s-v, so I have several paste-special buttons on the toolbar (including one that brings up the paste special dialog without having to visit the Edit menu). But this keyboard shortcut is good in Word and PowerPoint, at least the Alt-e-s, at which point I usually need to be reminded of the choices anyway.

   The nice thing about Juan Paulo’s technique (which I’ve used for a long long time) is that usually you’re usually already in the region with the mouse, perhaps having filled down a formula. You don’t have to put own the mouse to use the keyboard, or even travel a couple inches with the mouse.

5. Thanks. I frequently use paste special to remove formulas. Based on your idea, I developed a quick macro and put a button on my menu bar. I can now remove my formulas with 2 keystrokes.

   Stroke 1 – select starting cell in target column
   Stroke 2 – press macro icon on menu bar

   Sub cpy_wo_frmla()
   Dim tmp_rng As Range
   cl = ActiveCell.Column
   rw = ActiveCell.Row
   lst_rw = Cells(Rows.Count, cl).End(xlUp).Row
   Set tmp_rng = Range(Cells(rw, cl), Cells(lst_rw, cl))
   tmp_rng.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
   Application.CutCopyMode = False
   Cells(rw, cl).Select
   End Sub

6. I use paste special all the time to remove formulas. your post gave me an idea on how to develop a simple macro to get the job done in 2 keystrokes:

   1 – select starting cell in Column
   2 – press macro button on menu bar

   Here’s what I came up with.

   Sub cpy_wo_frmla()
   ‘ Macro copies and pastes special range wo formulas
   Dim tmp_rng As Range
   cl = ActiveCell.Column
   rw = ActiveCell.Row
   lst_rw = Cells(Rows.Count, cl).End(xlUp).Row
   Set tmp_rng = Range(Cells(rw, cl), Cells(lst_rw, cl))
   tmp_rng.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
   Application.CutCopyMode = False
   Cells(rw, cl).Select
   End Sub

7. Thanks!

   This has to go in a “Things I used to know, but forgot” list.

   I find myself formatting names using =PROPER()in an empty column then Copy/Paste Value in the original location. The next step is to clear the temp column.

   It might be nice to have a Move and Paste Value.
   (or go ahead and write the macro since I know I’ll be doing this exercise a million times in the future)

8. I like to take as many steps as possible to paste values.

   1. I start with a range of cells with formulas
   2. I save my file as a .CSV file
   3. I open Access
   4. I import the CSV file
   5. I rename some fields
   6. I export the table to Excel

   Tadaaa!!! No formulas!
   Feel free to share that cool trick with all your clients and friends

9. Mike –

   Couldn’t you just do this by manipulating recordsets in SQL Server?

   Kelly –

   This might be easier:

   Sub cpy_wo_frmla()
   ‘ Macro copies and pastes special range wo formulas
   If Typename(Selection) = “Range” Then
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
   Application.CutCopyMode = False
   End If
   End Sub

10. At work I’m almost 100% a mouse user but at home I use a laptop. I’ve become an obsessive touchpad user – without using the right and left buttons, which are both quite sticky. I get the features of the mouse, much more conveniently located. Tricks requiring right-clicking while dragging – such as Juan Pablo’s here – are the most difficult, but they can be done. (One of my favorite touchpad features is that just brushing the narrow strip on the right of the touchpad sends you scrolling up or down web pages and documents, at variable speeds.)

11. “a Paste Special operation takes 6 clicks right now, but only 3 in Office 12.”

    Interesting: I know this particular statement to be twaddle but went to read the blog anyway.

    Guess what, someone else already pointed out that with your toolbars set up it takes a lot less than 6.
    What’s not clear is how they count the clicks of course.

    My way:
    1 Click on Copy Icon
    2 Click on Paste Values icon

    that’s two clicks
    or 4 if you include selecting the cell to be copied then the target.

    I look forward to such flexibility in the new version.

    M

12. This fits nicely under the category of “Way too cool…”

13. Re: copy & paste values (in two steps: one click and one keyboard punch)
    I copy and paste values a lot and I wrote this little macro to speed it up. I use a keyboard shortcut to execute the macro: Ctrl/p (or whatever you choose). All you do is select the range to be copied and pasted (i.e. replaced) with values and then Ctrl/p.
    Store the macro in personal.xls and assign the keyboard shortcut to the macro.

    Sub CAPV()
    ‘
    ‘ Paste Values Macro
    ‘ This macro copies a selection and pastes values in one step – use Ctrl/p
    ‘
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

14. You can also add the Paste Values entry to the right click menu using VBA. Can’t remember how/why I came up with this originally:-

    Sub AddPasteValues()
    Application.CommandBars(“Cell”).Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    Application.CommandBars(“Row”).Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    Application.CommandBars(“Column”).Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    End Sub

15. I have a worksheet which runs microsoft queries when it opens. I want to write a macro, which will copy only the results of the query and paste-special in a new sheet and the queries should not get transported.

16. Hi Dick,

    I work in a AS-400 Mainframe where I have to selected a particular range every Minute. Iam unable to define the same. Pls help.

    Regards
    Prashanthy

17. This button is absolute first thing I add when I install excel…

    I also always customize the button to image and text. I rename the button &1

    Now I have 1 click and hot key (Alt-1) access to the most frequently used function for me in Excel.

    -Robert Sterbal

18. To All,

    I’m trying to create a toolbar for Paste-Special-Formulas, much like the Paste-Special-Values, and Paste-Special-Formats, which come with Excel. I’m using Excel 2003. Can anyone provide me with the VBA code to put into the paste special formulas toolbar?

    Thank you!!
    Bruce

19. Bruce – have a look at the third comment down (by Fadi Chalouhi)…

    My code to convert formulas to values is:

    Sub Value_Selection()
        Selection.Value = Selection.Value
    End Sub

    This has been discussed here and on other Excel sites before – seems to work a lot faster than PasteSpecialValues…

20. Bruce –

    Professional Excel Development (by Bullen, Bovey, and Green) has a nice example of a paste special toolbar.

21. This is exactly what I needed! I didn’t know that button for paste as value, neither this contextual right-click drag menu! THanks a lot!

22. Robert Sterbal’s tip (3/17/6) was EXACTLY what I needed. Undying gratitude, Robert!
    Troy

23. To think of all of the calories I’ve wasted over the last 5 and half years with all of the Right Arrow and Down Arrow, C, arrow, arrow, arrow and ESV ‘s I’ve been doing….

24. All good ideas, but all the keystroke accelerators attached to macros that I’ve tried cannot be undone! Alas, a good percentage of the time that I use Paste Values, I undo it after I have checked out the impact elsewhere on the spreadsheet.

Posting code? Use <pre> tags for VBA and <code> tags for inline.