In this tutorial, I’ll show you how to use VBA to paste values in Excel instead of formulas. You can bind this VBA macro to the Ctrl+Shift+V keyboard shortcut to make pasting even faster. The macro is smart enough to paste unformatted text, too.

This is one of the best tips I’ve seen in quite a while. I can’t believe I didn’t think of it! I want you all to extend a big thank you to wellsrPRO power user, Mitch, for submitting this macro to me via the wellsrPRO add-in.


Reader’s Note: This article is part of a series featuring macros submitted by my incredible wellsrPRO community members. If you’re an existing wellsrPRO member, don’t forget to submit your own macro to the wellsrPRO community using the Share My Macros button. wellsrPRO users can automatically import this community submission, along with any other macro posted on wellsr.com, directly into their spreadsheet. Just look for “Community Submissions” in the Auto-Import dropdown menu.


VBA Paste Values or Unformatted Text

Created by Mitch

'==================================================================================================
' ## Paste as values or unformatted text from within or outside of Excel.
'    Tip: assign this to a keyboard shortcut: Ctrl+Shift+V
'    Developer: Mitch
'==================================================================================================
Sub PasteValues()
    '// first test if pasting from within excel, if an error then
    '   proceed to paste as unformatted text
    On Error Resume Next
    
    '// Paste as values
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    '// Paste as unformatted text
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False 
End Sub

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

How the VBA Paste Values Macro Works

Mitch does a very clever thing with this macro. He tries to paste the contents of your clipboard two different ways. Let’s look at an example. In this example, the range A1:B4 contains raw numbers and the range D1:E4 squares those numbers using a formula. In the example, we have the Excel cells with formulas copied to our clipboard and we want to paste the values to another range.

Cells with Formulas

First, we’ll show what happens if you copy and paste the cells like normal, using Ctrl+c and Ctrl+v.

Formulas are pasted

Excel tries to outsmart you by pasting the formulas down to the relative cells. The range D6:E9 now contains formulas trying to square the results in cells A6:B9. That’s not what we wanted! We want to paste the values themselves in the range D6:E9.

To do that, we’ll run Mitch’s PasteValues macro. The first thing his macro tries to do is paste the formulas as values. That works great! It will then try to paste the same content as unformatted text, but this won’t do anything and the output of the first attempt remains. Trying to paste cell values as unformatted text would normally generate an error, but Mitch bypassed the errors using the On Error Resume Next code, so he gets these lovely results:

VBA Paste Values

So why does Mitch try to paste the results as unformatted text if it doesn’t work?

Well, this is where Mitch’s brilliance really becomes evident. To show you what I mean, we’ll pretend we have a table in Microsoft Word, like the one below:

Word Document Table

We want to copy and paste the values from the table into Excel. If we copy the table from word and paste it normally, using Ctrl+v, Excel will again try to outsmart you by pasting the format, ugly border and all, into Excel. You’ll be left with something rather hideous, like this:

Paste from Word Format Borders

Now we want to do the same thing, but instead of pasting with Ctrl+v, we want to paste using Mitch’s macro. Just like earlier, Mitch’s PasteValues macro will attempt to paste the contents of your clipboard as values only. Since the contents of your clipboard didn’t originate from Excel, this would normally produce an error.

However, since Mitch bypassed the errors using the On Error Resume Next code, it tries the second method of pasting. This time, the second method, which pastes with unformatted text, works perfectly. No error is generated and you’re left with clean, unformatted, values pasted into Excel:

Excel paste unformatted text


Creating your Keyboard Shortcut

The icing on the cake is when you bind the PasteValues macro to a keyboard shortcut so you can paste values into Excel using VBA with just a keystroke. I’ll show you how!

From your Developer Tab, click the “Macros” button (Alt+F8). Select “PasteValues” then click “Options.”

VBA Macro Options

In the Macro Options screen, type a capital V. Your screen will look something like this:

VBA paste values ctrl shift V

Press OK and close the Macros screen. Now, anytime you want to paste values in Excel instead of formulas, all you have to do is press Ctrl+Shift+V on your keyboard! It’s so convenient!

If you’re a wellsrPRO user, you can save this macro to your favorites and import it into any spreadsheet you want so the option to paste values is always available.


I hope you enjoyed this tutorial and if you see Mitch in the comments section of one of my articles, take a moment to thank him for this brilliant macro.

After that, please share this article with your friends on Facebook, Twitter, and Google+.

To free up your time in the office, visit my Excel Add-ins page for some great automation products, including wellsrPRO.


About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.