These two macros will save you a lot of time. You will used them every time when you need to copy something from external source (like Internet page) into a Word or Excel document, but you do not need all the colors and formatting. You just need the text.
Normally, you would do this with Paste Special, but this is several awkward clicks and many seconds lost. Over the course of the day this is a huge inefficiency.
Let’s write a macro for Word first. As of 2012-01-11 WED, I adopted this little macro as final version:
Sub PasteText() On Error GoTo Unicode Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _ Placement:=wdInLine, DisplayAsIcon:=False GoTo Done Unicode: Selection.PasteAndFormat (wdFormatPlainText) Done: End Sub
This macro was tested with Word 2007, and it can handle paste from Web and from Visuals Studio. First statement (Selection.PasteSpecial) handles rich text formats, and second statement (Selection.PasteAndFormat) deals with pasting Unicode strings.
This is a very simple macro. It works in all Word versions, including Office 2010. It works multiple times, so you can paste the same string over and over again. Who can suggest a better way of doing this without using dreaded “GoTo” statements?
Regular paste is Ctrl+V. I recommend to map this macro to Alt+V key combination.
To map your new macro to a keyboard combination in Word 2007 do these simple 10 (Ten!) steps:
01 Click Office Button 02 Click Word Options button 03 Click Customize button 04 Click Customize button 05 Pick Macros in Categories drop-down list 06 Select your new macro in Macros drop down list 07 Position your cursor into Press new shortcut key 08 Press Alt+V 09 Click on Assign button 10 Press Close and Cancel
Now let’s proceed to Excel. In Excel open visual basic (Alt+F11) and paste this code into one of the PERSONAL.XLS modules:
Sub PasteValues() On Error GoTo ErrorHandler If Application.ClipboardFormats(1) <> -1 Then If Application.CutCopyMode Then 'Paste values, if in CutCopyMode Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Else 'Paste text, if from external source ActiveSheet.PasteSpecial Format:="Text" End If End If GoTo FinishMacro ErrorHandler: 'Error will be caught, if there is nothing to paste Resume Next FinishMacro: End Sub
As you can see in Excel, it is a little more work. The problem here is that that there are two possible scenarios. One is Copy and Paste Special inside Excel, and the other is Copy and Paste Text from outside the Excel. And this macro should handle both scenarios.
We are suggesting to use Shift+Ctrl+V combination for this macro. To assigns a keyboard shortcut to a macro in Excel, use this four steps:
01 Press Alt+F8 to open Macros dialog box 02 Press Options button 03 Press Shift+V and 04 Press OK and Cancel
Moment of truth. Now you can start shaving precious seconds, when you copy formatted text from outside sources into your Word or Excel documents.
2016-02-13 SAT – Updated after series of comments from Chuck =======>:
Of course, you need to place your macro inside PERSONAL.XLSB file for all instances of Excel to have access to this functionality.