lose formatting (border) in excel cell after pasting from word

[Guest]

When I paste from word, I lose left border of cells into which I paste. As
this is invoice, causes alot of time, then reformatting cells.

All help appreciated

 

[Frank Kabel]

Hi
have you tried using 'Edit - Paste Special - Text only'

 

[Guest]

Thanks Frank

Unfortunately I am pasting alot of different items via the clipboard.
Perhaps there is a way of switching 'match destination formatting' on as the
default?

Russell

 

[Frank Kabel]

Hi
AFAIK there's no such switch

 

[Guest]

There is not such a switch. However, you can create a macro to accomplish
this.

From Excel, hit ALT + F11. This will open the VB Editor. In the editor go
to the Insert menu and select Module. Put the following code into the new
module:

Sub PlainPaste()
ActiveCell.PasteSpecial xlPasteAll
End Sub

Close the VB Editor and go to Tools --> Macro --> Macros. In the Macros
dialog box, make sure the PlainPaste macro is highlighted and hit the
"Options" button on the right. You can specify the hot key (I have mine set
to CTRL + e) here. Hit OK, then close the Macros dialog.

Last step, go to Tools --> Options and click on the Security tab. Click on
the "Macro Security" button at the bottom and set your security to "Medium".
Hit OK, and OK, and you're done!

From then on, whenever you are in that particular Excel document, hitting
CTRL + E (or whatever your hotkey is) will paste the contents of the
clipboard...MINUS formatting information.

Note that this will ONLY affect text. In addition, if you are copying FROM
another Excel worksheet, this macro will only work if you are copying one
cell at a time.

 

[Guest]

Sorry, the code I gave will not strip the formatting if you are copying from
another Office application. Change it to this:

Sub PlainPaste()
ActiveCell.PasteSpecial xlPasteValues
End Sub

Note that, with the above code, you will likely get an error if the data you
are pasting is from a non-Office application.

I will try to figure out some error handling so that one macro will work
regardless of the source of the data to be pasted.

 

[Guest]

Looks like I posted a wee bit soon. Here is the code with error handling so
you should be able to paste from any application.

Sub PlainPaste()

On Error GoTo Non_Office_App
ActiveCell.PasteSpecial xlPasteValues

Exit Sub

Non_Office_App:
ActiveCell.PasteSpecial xlPasteAll

End Sub