Excel Paste Special macro shortcut key for both objects and text

Today is the day I decided I had enough of Excel’s lack of a Paste Special shortcut key.

Pasting Annoyances

There are several ways to Paste Special via the keyboard but all of them involve multiple key press sequences.  This may have been simple enough, but when the source of the copied content is from a source other than Excel the Paste Special dialogue box differs, and the last couple keys in the sequence differ as well.


Fig. 1 The Excel source Paste Special dialogue


Fig. 2  The Microsoft Word source Paste Special dialogue

To create a shortcut key for a function (that is not represented by a menu button) we must first create a macro for that function.

Macro Errors

A simple macro like:

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

works fine if you are copying cells from Excel or objects from Word, Outlook, etc.  If you are copying text, from inside an Excel cell or elsewhere, you will get an error:


Fig. 3  Error pasting text from outside Excel into Excel

You may also get this error if you run the macro via the Developer -> Macros dialog box.  So remember to run your macro via the shortcut, which we will create below.

Paste Special Macro

To create a macro that can handle pasting both types of data we can use an error catching routine.  Also, since we now know that running a macro will clear the undo stack, we should include code that will give us some protection from pasting an erroneously.

' Custom data type for undoing
    Type SaveRange
        Val As Variant
        Addr As String
    End Type
' Stores info about current selection
    Public OldWorkbook As Workbook
    Public OldSheet As Worksheet
    Public OldSelection() As SaveRange
Sub PasteValues()

' Set shortcut to Cntl+Shift+V, for example
' Works for Outlook and Chrome AND Excel

' Abort if a range isn't selected
    If TypeName(Selection) <> "Range" Then Exit Sub

' The next block of statements
' save the current values for undoing
    ReDim OldSelection(Selection.Count)
    Set OldWorkbook = ActiveWorkbook
    Set OldSheet = ActiveSheet
    i = 0
    For Each cell In Selection
        i = i + 1
        OldSelection(i).Addr = cell.Address
        OldSelection(i).Val = cell.Formula
    Next cell

' Start paste function
    On Error GoTo ValuesFail
    ' Works for Excel and Outlook, but not Chrome
    Selection.PasteSpecial Paste:=xlValues
    ' Specify the Undo Sub
    Application.OnUndo "Undo the macro", "UndoMacro"
    Exit Sub
    On Error GoTo TextFail
    ' Works for Outlook and Chrome, but not Excel
    ActiveSheet.PasteSpecial Format:="Text"
    ' Specify the Undo Sub
    Application.OnUndo "Undo the macro", "UndoMacro"
    Exit Sub
    On Error GoTo PasteFail
    ' Specify the Undo Sub
    Application.OnUndo "Undo the macro", "UndoMacro"
    Exit Sub
    MsgBox "Complete Failure"
End Sub
Sub UndoMacro()
' Reinstates data in the selected range

' Tell user if a problem occurs
    On Error GoTo Problem

    Application.ScreenUpdating = False

' Make sure the correct workbook and sheet are active

' Restore the saved information
    For i = 1 To UBound(OldSelection)
        Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
    Next i
    Exit Sub

' Error handler
    MsgBox "Can't undo macro"
End Sub
Sub RevertFile()
' From http://www.excelforum.com/showthread.php?t=491103

    wkname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    ActiveWorkbook.Close Savechanges:=False
    Workbooks.Open Filename:=wkname

End Sub

The code above will allow you not only to paste both text and objects into Excel, it will also allow you to undo changes to data in the range selected for pasting.  Even if you erroneously paste over needed data, you can run the RevertFile macro to reload your worksheet to its last saved state.

In case you would like to experiment more with what code works with what sources you can use the code below.

Sub PasteOutlook()

' Set shortcut to Cntl+Shift+B, for example
' Works for Excel and Outlook, but not Chrome

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

' Set shortcut to Cntl+Shift+E, for example
' Works for Excel, but not Outlook or Chrome

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub PasteChrome()

' Set shortcut to Cntl+Shift+C, for example
' Works for Outlook and Chrome, but not Excel

    ActiveSheet.PasteSpecial Format:="Text"
End Sub

One more thing:  save these macros in PERSONAL.XLSB so that your new Paste Special shortcut is available in all your spreadsheets.

I hope these macros will help you as much as they will me!

Beware of Spirit Airlines’ boarding pass policy

Spirit Airlines

Spirit Airlines

I’m not going to bore you with my airline horror story, but I recommend you take note of section 2.3.1. of Spirit Airlines Contract of Carriage if you are flying that airline.  They state that you must obtain a printed boarding pass 45 minutes prior to boarding for most major airports.  Compare that with 30 minutes (electronic tickets are valid) for most other major airlines, for example section Rule 5 D) 1) a) of United Airlines Contract of Carriage.

I for one won’t be flying Spirit again.

Never buy Bose

The left speaker in my $300 Bose QuietComfort 15 stopped working after less than one and a half years.  When I called Bose to ask for a refund or exchange they informed me that their warranty period was one year.  Although after a lot of negotiation they finally offered to exchange them for $9.27 shipping I won’t be buying Bose again.  And I was just checking their website for a new home stereo system…

Bose QuietComfort 15 Acoustic Noise Cancelling Headphones

Bose QuietComfort 15 Acoustic Noise Cancelling Headphones

By a Tesla Model S, Save $1000

Tesla Model S

Tesla Model S

Here’s an email I received from Elon today reviewing the virtues of the Tesla Model S.  He also said you can save $1000 by using this link, http://ts.la/roel6158 (expires on Oct 31, 2015).

The first thing to mention is that you can buy a Tesla online, just like ordering a computer or a book. No need to go to a store. Moreover, if you lease it, the Model S comes with a happiness guarantee. If you don’t like the car for any reason, you can just give it back.

A survey of Tesla customers by Consumer Reports found that 98% expected that their next car purchase would also be a Tesla, much higher than for any other car. I feel the same way about my Tesla, which is why I’m recommending one for you.

Here is some background from Tesla about why you might want to buy a Model S:

Starting at $575/month after gas savings, the Model S is still a relatively expensive car, but here is what makes it worth the price:

Maximum Safety

The single most important thing to know about the Model S is that it is literally the safest car on the road bar none. It didn’t just receive five stars in every category and subcategory of safety (including for pedestrians), which about 1% of other cars do—the Model S recorded the lowest probability of injury of any car ever tested by the U.S. government across all passenger vehicles, including minivans and SUVs.

In addition, every Model S comes standard with automatic emergency braking, as well as blind-spot and forward- and side-collision warning systems to prevent accidents in the first place.

Speed and cool features are nice, but nothing is more important than the safety of you, your family and your friends. For more info, check out this blog: http://www.teslamotors.com/blog/tesla-model-s-achieves-best-safety-rating-any-car-ever-tested


Since the Model S doesn’t need a big combustion engine, radiators, exhausts or catalytic converters, it has tremendous cargo capacity. With both a trunk in the front and a trunk in the rear, it has more storage space than any other sedan and more than most SUVs.

There is so much space in the back that you can have an optional fold-flat, rear-facing third seat, allowing you to carry five adults, two children and luggage in the front trunk. Tesla installs a high strength steel bumper to enable the car to take highway-speed impacts in the rear without permanent injury to the third row.

You can also carry several sets of skis, bikes and other equipment using the built-in attachments for the roof rack.

Other features that improve utility are a dynamic air suspension that remembers where it needs to raise itself, based on when you last pressed it. With this, you can raise the car above the snow and get through anything SUVs can handle. Tesla’s biggest per capita sales are above the Arctic Circle in Norway.


Tesla received the highest customer feedback rating for service of any car brand in production: http://www.consumerreports.org/cro/magazine/2015/03/best-places-to-get-your-car-repaired/index.htm

A big reason for this is that Tesla intentionally operates its service centers at break-even. We don’t believe in profiting off our product if it is not working.

Our service centers are located throughout North America and Canada, with Mexico coming soon: http://www.teslamotors.com/findus/service

If there isn’t one near you, no problem: our Tesla ranger service will take care of you wherever you are.


Even the basic Model S has great acceleration and handling. This goes all the way up to the P90D version, which does a record-breaking 0 to 60 mph in 2.8 seconds and a 10.9-second quarter mile, far beyond the capabilities of any other four-door car and faster than a Porsche 911 Turbo. The P90D in ludicrous mode can accelerate at 1.1 g’s, which is faster than falling.

What makes our handling superior to gasoline cars is that the center of gravity (cg) is extremely low, because the battery pack is in the floor pan, centered between the axles. This also means that a key engineering measure of handling, the polar moment of inertia, is almost perfect. No other production car in the world has a more ideal placement of mass for optimal handling. The Model S has Newton on its side.

With Tesla’s dual motor all-wheel drive, the traction and handling in every kind of weather are a step change better than gasoline all-wheel drive. Instead of having a simple mechanical shaft connecting front and rear, a Tesla actually has a motor in the front and a motor in the rear, so it can dynamically shift torque front to rear at the millisecond level, effectively providing digital traction control an order of magnitude more precise than mechanical linkages.

Like an airliner, it also enables motor redundancy. If one motor encounters a fault, you can simply drive with the other one, rather than being stuck on the side of the road.

Cool features

The Model S has an easy-to-use 17-inch touchscreen computer that is always improving itself and the rest of the car via free over-the-air updates.

The car learns your habits and will automatically set the cabin temperature to your preference when it thinks you are about to use the car. If you enable the calendar function, the car will show you your appointments on a big, easy-to-read screen, and you can just tap an address to navigate there. No more fumbling with a tiny phone.

Navigation includes real-time traffic data from the car’s cell connection, and it will dynamically adjust your route as traffic conditions change. In the morning and evening, it will alert you and offer an alternate route if your normal route is congested.

You can also ask the car to play any song or favorite band at any time just by holding down the voice button. It also has lots of comedy sketches available, ranging from Monty Python to Jim Gaffigan.

Coming in a few weeks via an over-the-air update are the highway autosteer and parallel autopark functions. When asked, the car will automatically control steering going down the freeway, dramatically reducing driver workload. It will also automatically parallel park with precision.

In a few months, you will be able to press a button on your Tesla phone app and the car will open your garage and put itself to bed. You will also be able to summon it from your garage if it isn’t plugged in. It needs the Tesla Snakebot for that! https://twitter.com/TeslaMotors/status/629305813912326146

What about charging?

The Model S has a charger built-in, so most owners just plug into a wall socket at their home or office. It can use anything from a standard 110V outlet at 1.5 kW all the way to the Wall Connector at 20 kW. Most customers just install a simple 240 V dryer socket in their garage, which all electricians can do, and it works perfectly.

For long distance, you get to use the free Tesla long-distance Supercharger network, located near restaurants and amenities. Typically the time spent on recharging is about 25 to 30 minutes after three hours of driving, which is about right. If you start a trip at 9am, by noon most people want to stop to use the restroom, have lunch or coffee and be on their way. By the time you come back your car is ready to go.

The Supercharger network covers the lower 48 continental states in the U.S. and parts of southern Canada, soon to include Mexico. As mentioned above, the Tesla Superchargers really are free to use for life. You could go on a road trip (rear seats fold flat into a great bed), pack some food and leave your wallet at home. Map: http://www.teslamotors.com/findus/supercharger

Expert opinions

The Model S has won almost every award offered for a vehicle, including Motor Trend Car of the Year, Automobile Car of the Year and Consumer Reports’ best car in the world (two years running).

Moreover, Consumer Reports gave Model S the highest rating of any car in its long history: 99/100. The reason CR is the most trusted source for buyers is that they don’t take advertising, they buy a car secretly and at random (so they know it is a normal car) and they test it rigorously for months before reaching a rating. There is no more objective source.

Environmental Impact

Building a Model S produces roughly the same CO2 as a gasoline car of similar weight, however it is far more CO2-efficient in driving, which is what really matters over the lifetime of the car.

The EPA rates the efficiency of Model S as equivalent to 90 mpg—making it twice as energy-efficient as a compact hybrid even when factoring in power plant emissions. The current Model S number will steadily improve over time as older power plants are phased out in favor of clean, renewable energy.

However, if you install even a small solar panel on your house or garage, you will actually produce more electricity during the week than you consume with your car, making your automotive carbon footprint unequivocally zero or better. And, in the unlikely event of a zombie apocalypse, you will still be able to charge and drive your car!!