In the Macro Options screen, type a capital V. Select “PasteValues” then click “Options.” I’ll show you how!įrom your Developer Tab, click the “Macros” button (Alt+F8). 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. No error is generated and you’re left with clean, unformatted, values pasted into Excel: This time, the second method, which pastes with unformatted text, works perfectly. However, since we’ve bypassed the errors using the On Error Resume Next code, it tries the second method of pasting. Since the contents of your clipboard didn’t originate from Excel, this would normally produce an error. Just like earlier, the PasteValues macro will attempt to paste the contents of your clipboard as values only. Now we want to do the same thing, but instead of pasting with Ctrl+v, we want to paste using the macro. E for Paste, S for S pecial, and E for Transpos e. Remember, Alt is the command to activate the Ribbon shortcuts. Alernatively you can use Alt > E > S > E. Simply add Alt for Paste Special and use E for Transpos e. You’ll be left with something rather hideous, like this: This Excel Shortcut pastes and transposes. 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. We want to copy and paste the values from the table into Excel. To show you what I mean, we’ll pretend we have a table in Microsoft Word, like the one below: Well, this is where this small macro’s brilliance really becomes evident. So why we try to paste the results as unformatted text if it doesn’t work? Trying to paste cell values as unformatted text would normally generate an error, but we’ve bypassed the errors using the On Error Resume Next code, so we get these results: 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. The first thing the macro tries to do is paste the formulas as values. To do that, we’ll run the PasteValues macro. That’s not what we wanted! We want to paste the values themselves in the range D6:E9. The range D6:E9 now contains formulas trying to square the results in cells A6:B9. In the example, we have the Excel cells with formulas copied to our clipboard and we want to paste the values to another range.įirst, we’ll show what happens if you copy and paste the cells like normal, using Ctrl+c and Ctrl+v.Įxcel tries to outsmart you by pasting the formulas down to the relative cells. In this example, the range A1:B4 contains raw numbers and the range D1:E4 squares those numbers using a formula. PasteSpecial Format : = "Text", Link : = False, DisplayAsIcon : = False End Sub How the VBA Paste Values Macro Worksįirst we to paste the contents of the clipboard two different ways. PasteSpecial Paste : = xlPasteValues, _ Operation : = xlNone, SkipBlanks : = False, Transpose : = False '// Paste as unformatted text ActiveSheet. ' 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. Note: You need to hold Ctrl + ALT key together and then press “V” to open paste special dialogue box.'= ' # Paste as values or unformatted text from within or outside of Excel. Now hold “Ctrl + ALT” and press “V” to open the “Paste Special’ dialogue box. The second shortcut key is to open paste special dialogue box is “Ctrl + ALT + V” this will open up the paste special dialogue box, and from there on, press “V” to select the “Values” option.Ĭopy the range of cells to be pasted, then select the targeted cell where we need to paste as values. #2 – Paste Values Using Shortcut Key “Ctrl + ALT + V” After selecting the “Values” option, click on the “Ok” button to get only values.From this list, we need the option of “Values” so to select this option in this window, we can press the shortcut alphabet “V,” and it will choose the “Values” options in this “Paste Special” window.Now press the shortcut key “ALT + E + S” (all keys one by one, do not hold any key), it will open up a wide variety of paste special options.Now select the cell or range of cells where we need to paste the copied data.Follow the steps to paste values in excel using shortcut key.
0 kommentar(er)
