The Robservatory

Robservations on everything…

 

An even easier way to use Excel’s Paste Special dialog

I recently explained how to use the keyboard in Excel’s Paste Special dialog box, and this is a great timesaver on its own. But I use Paste Special a lot, especially with Formats, Formulas, and Values, so I made those three even easier to use via the keyboard…

Each one has its own direct keyboard shortcut, courtesy of Keyboard Maestro. Here’s how I set it up; these instructions should work (with some changes, of course) for any app that can script keystrokes.

First, I created these macros in an Excel group, so they’re only active when Excel is frontmost (no need to create global hot keys that you only use in one program). The actual macros are pretty trivial:

  1. Send Command-Control-V to bring up the Paste Special dialog
  2. Pause just long enough for the dialog to appear onscreen
  3. Send the chosen shortcut key—T, F, or V in my vase
  4. Send the Return key to execute the action

Then I just assigned each one to the same key used within the dialog, but with Command and Option to make it usable from anywhere within Excel.

Here’s how all of that looks in Keyboard Maestro for the Paste Special – Values macro:

These macros don’t save a ton of time—they save literally two key presses per use—but they help with “flow” when I’m working on something: I don’t have to stop everything for a dialog that sits there onscreen until I remember to press a couple of keys. Instead, I only have to face that dialog when I’m using one of my lesser-used Paste Special commands.

6 Comments

Add a Comment
  1. Creating a set of Excel macros might be easier to use. For example, here’s paste formats (without any error checking):

    Sub PasteFormats()
    Selection.PasteSpecial Paste:=xlFormats
    End Sub

    You can then assign a keystroke to that in Excel (I use command-shift-option-f).

  2. Internal macros are definitely a solution. But they’re harder to explain to non-Excel power users, and you have to remember to port them across to new machines/OS installs. I used to use a ton of them, but I’ve moved the vast majority out to third party tools when I can.

    -rob.

    1. All true. But I don’t switch machines or do clean OS installs very often, and I’ve been trying to *get rid* of third party tools where possible, so the macro option works well for me. Good to have multiple ways of doing things though.

  3. I found your page looking for exactly this kind of solution for Paste Values in Excel.

    I have replicated your macro exactly, but I can’t figure out why it does not execute any steps past the first (Type the Command-Control-V Keystroke).

    I’m relatively new to Keyboard Maestro. Is there a good reason why the macro doesn’t execute the subsequent three actions? (The macro does not appear to be “hung” in any way, and I can clear the dialog box easily enough by actually typing V, then return, to finish the Paste Values command)

    I am running Keyboard Maestro 8.0.3, Excel 15.39 (171010) Office 365 Subscription (I believe they the most current versions of each).

    Thanks for any help, here. Your tips on this and other pages are great!

Leave a Reply

The Robservatory © 2017 Built from the Frontier theme