Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mouse continues to select after clicking on a cell in excel. | Excel Discussion (Misc queries) | |||
Problems with cell formatting | Excel Discussion (Misc queries) | |||
Can I copy a combo box in Excel 2002 with a relative cell link? | Excel Discussion (Misc queries) | |||
Why won't my conditional formatting display in the cell | Excel Discussion (Misc queries) | |||
excel locks up after selecting a cell | Excel Discussion (Misc queries) |