Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Reverse_Solidus
 
Posts: n/a
Default lose formatting (border) in excel cell after pasting from word

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.

"Frank Kabel" wrote:

Hi
AFAIK there's no such switch :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"russweb" schrieb im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"russweb" schrieb im

Newsbeitrag
...
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




  #2   Report Post  
Reverse_Solidus
 
Posts: n/a
Default

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.

"Reverse_Solidus" wrote:

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.

"Frank Kabel" wrote:

Hi
AFAIK there's no such switch :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"russweb" schrieb im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"russweb" schrieb im

Newsbeitrag
...
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




  #3   Report Post  
Reverse_Solidus
 
Posts: n/a
Default

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

"Reverse_Solidus" wrote:

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.

"Reverse_Solidus" wrote:

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.

"Frank Kabel" wrote:

Hi
AFAIK there's no such switch :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"russweb" schrieb im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"russweb" schrieb im
Newsbeitrag
...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mouse continues to select after clicking on a cell in excel. Fletch Excel Discussion (Misc queries) 4 February 22nd 05 03:27 AM
Problems with cell formatting Jecakias Excel Discussion (Misc queries) 1 February 18th 05 08:04 PM
Can I copy a combo box in Excel 2002 with a relative cell link? Bozo Excel Discussion (Misc queries) 1 February 17th 05 03:05 AM
Why won't my conditional formatting display in the cell Cashius War eagle Excel Discussion (Misc queries) 3 February 15th 05 09:38 PM
excel locks up after selecting a cell sandenscot Excel Discussion (Misc queries) 7 January 28th 05 08:27 PM


All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"