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.
Word
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
Excel
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.
Chuck, try this:
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:=”Texto unicode”, Link:=False, _
DisplayAsIcon:=False
End If
End If
GoTo FinishMacro
ErrorHandler:
‘Error will be caught, if there is nothing to paste
Resume Next
FinishMacro:
End Sub
Thank you so much! This is what I needed to copy from external source. Majority of forums I went to search for answers show solutions for copying from excel workbook only.
Yes. Perfect. I also share PERSONAL.XLS across multiple computers over the network. Still, I always place macros that apply to entire Excel application into PERSONAL.XLS for all user to have access to. If several user open Excel at the same time, they simply receive a warning, and they have to open the file in a Read-Only mode.
Thanks so much for your response, Udar.
I’m using Excel 2013 for a spreadsheet in a shared network drive (so I have placed the macro into a standard module rather than a PERSONAL.XLS module).
I needed to disable all paste-with-formatting options as, despite being asked not to, other users have forgotten and out of habit have pasted in his way, which causes havoc with my conditional formatting and protected cells.
So, I have assigned the macro to ctrl+v, to overwrite the standard paste for that command.
Users will be pasting from the internet, Word documents and other Excel spreadsheets, and whilst the macro works brilliantly for the first two (and for copy-pasting within the same worksheet) it will not work across spreadsheets or across workbooks. It’s not that it goes back to pasting with formatting in theses instances – it just doesn’t paste at all.
I’m pretty new to Excel so I’ll admit to having not much clue about what the problem could be!
Any help would be much appreciated – I’ve tried other macros I’ve found on the net but yours has come the closest to doing what I need!
Thanks
Chuck
Hi Chuck,
Thank you for your question. I cannot reproduce the behavior that you are describing. I am using latest Office 365 and Excel 2016. I just tested several scenarios:
– I can paste as plain text into other instance of Excel (with my custom Shift+Ctrl+V),
– I can paste into other applications (using normal Ctrl+V)
– I can even paste into applications at a remote desktop session (using normal Ctrl+V).
Here is the code one more time:
Sub PasteValues()' Keyboard Shortcut: Ctrl+Shift+V
' 2005/12/18 Sun
' 2009-10-30 FR Check Application.CutCopyMode
On Error GoTo ErrorHandler
' 2011-05-05 TH Check, if Clipboard is empty
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
Please describe you situation in more details.
Hi,
I used the code for Excel and it does pretty much what I need – except for pasting from anywhere in Excel other than the worksheet copied from, i.e. I can copy and paste within a worksheet, but not from another worksheet or another Excel workbook. It just pastes nothing when I try. Is there any fix for this at all please?
Thanks
I’m not familiar with VBA syntax and I’ve found your macro very useful. THANK YOU !
I’ve also found another way to do it but I’m not sure if that option pastes in UNICODE format. If it works is as quick as yours. It consists of setting the default paste options to your preferences in Word Options. The steps would be these (I have Word 2007 in Spanish, i.e. the original names in English may differ from these):
01 Click Office Button
02 Click Word Options button
03 Click Advanced
04 Go to Cut, Copy & Paste Options
05 Select “Keep text only” in the different options to paste between documents or to paste from another applications
06 Press Accept
Perhaps you could confirm it. I don’t know how to check the type of pasting (Unicode or plain text)
THANKS AGAIN !!
Manuel