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!

FAQ for Media.net/Yahoo/Bing Contextual Ads Program


I, as a publisher, use Media.net to fill some of my advertising inventory.  If you are considering using Media.net, or have recently begun using them, the answers below may help you understand some of their payment processes.

1.       How can I discern if a particular wire transfer was from Media.net?  My bank statement does not contain that information.

The payment we make is made via ‘SecureN1 transactions LLC’. This should help you identify payments from us.

2.       Do you charge me for wiring money to me?  If so, how much?  Do you charge for Paypal payouts?  If so, how much?

We do not charge you for the wire. However, the bank processing the wire may have charges. Again, we do not charge for Paypal payouts but Paypal may have charges in some cases.

3.       Why do you not offer ACH payouts?

We currently do not have this method of payout. However, it is being considered.

4.       How will I know what period of time was covered by a particular wire transfer?  Is there a page within my account that I can see such a correlation?

We make payments on a NET 30 basis. For example, you will receive the payment for the month of February on or around the 30th of March. You can see the previous months earnings on the dashboard. Additionally, the narration accompanying our payments also mentions the month for which the payment is made.

Elto.com Review: The Joke that Elto is

I recently was looking for someone to migrate my self-hosted WordPress site, EuropeanParty.com, to one hosted by WordPress.com.  The (one!) requirement was to make it look as similar to the self-hosted site as possible without incurring any WordPress.com fees.

I found on WordPress’s own site that they recommend Elto.com.

The short story is that Elto is not much better than the various (mainly) foreign freelancers that hold themselves out to be WordPress experts – in other words, they have NO idea what they are talking about.  If you want the long story (i.e. the transcript) leave me a comment.

I ended up having to do it myself.  Below is one screenshot of the self-hosted site and one of the Wordpress.com hosted site.

Self-hosted self-hosted


Transfer Contacts from Android to Windows Phone

Android to Windows Phone

I recently lost my Samsung Galaxy S III and decided to replace it with the Nokia Lumia 1020 and its killer camera, which required me moving from Android to Windows Phone 8 (WP8).

I had anticipated moving to another phone before I lost my S3 so all of my contacts were saved online in my Google account with Google Contacts.  At first it appeared that my transition would be import and export free:  all I would need to do is sign in to my Google account from my WP8 phone.  But upon checking my contacts after syncing my Google account I saw that most of them were missing most of their info, notably phone numbers.

I did a lot of research, including reading some announcement that Google Sync will be discontinued for free Google Apps users, but nothing told me why most of my contacts were defective.  So I looked at the actual content of the Google Contacts format by downloading the file and eventually found that the reason for the missing content was capitalization of some of the field names.

If you have the same issue do this to fix it:

  1. Download your Google contacts via More -> Export… -> Google CSV format.
  2. Open the file with a text editor, e.g. Notepad,  (Excel won’t work for this task.)
  3. Do a case sensitive replacement for:
    HOME -> Home
    MOBILE -> Mobile
    CUSTOM -> Mobile
    MAIN -> Mobile
    WORK -> Work
  4. Save the file,
  5. Delete all your online Google contacts,
  6. Upload your newly decapitalized contacts file, and
  7. Finally sync your Google account on your WP8 phone.

That solved my problem.  Hopefully it will solve yours too!

You may find that you have more field names for certain phone numbers:  maybe pager, company, or whatever.  Sentence case these field names also.

Let me know if this helped you transfer your contacts.

Good luck!

Carbonite is Useless (Review)

I don’t have much to report here since I my experience with Carbonite occurred a few months ago and I’ve forgotten many of the details. But I’d like to report that Carbonite was absolutely useless.

Apparently Carbonite had backed up several gigabytes of data for me, but I couldn’t get to any meaningful amount of it. Nor could I access particular files for that matter.

I have a very high speed internet connection; speedtest.net tells me upwards of 50 Mbps. However, it took me 24 hours to download less than a couple hundred Mb from Carbonite. I called their tech support several times the next day to ask them how I could download a particularly important file I needed. They directed me to the search function of their desktop control panel. It turned out to be unable to search my files effectively. It also crashed many times.

So after spending over an hour on the phone with their tech support over the span of several calls and days I decided to ask for a refund for the unused 2 years on my 3-year plan. When they should have offered me a full refund they told me I had to spend more time with their tech support winding my way through 2 more levels of support despite having worked my way to what I already thought was level 3 support.

Well, I’m using SOS Online Backup now. If I ever need to access my data I’ll let you know how it goes.

How to Host a Successful Design Contest on 99designs or crowdSPRING

Here are my tips for hosting a successful design contest:

  1. Purchase a Bronze/Economy package but state in your contest title that you are doubling the winnings to $400 (so you’ll award an additional $200; 99designs takes $99 from the $299 Bronze package) and that they will be distributed to designers that contribute elements to the final design.  Do either that or make your contest blind, it’s only fair.  It also should be blind if you offer over $1000.
  2. Purchase a Bronze package, but state that you will award an additional $200 (or $100 or whatever) for another reason too:  99designs and crowdSPRING are a complete ripoff when it comes to their commission.  For the Silver/Standard package they charge $200, or 40%!
  3. Definitely DO invite great designers who are part of the community.
  4. Consider inviting only proven designers for a private contest so that you don’t waste your time with lower quality designers.
  5. If you do not host a private contest, quickly eliminate designers that you determine by their portfolio cannot finish the design so as not to waste time communicating with them.
  6. Don’t invite designers who do not already have a successful account with the community; if they don’t already have an account they may not work well within that system even if they’ve proven their worth as a good designer.
  7. Guarantee your contest immediately if possible, and state that it is guaranteed in the contest title.

Those are all the tips I’ve got for now.