Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain formatting of Excel sheet when copying data into it
How do I copy data from different sources (in different fonts) into my
spreadsheet and make sure my spreadsheet retains its own font i.e. the incoming data should lose the font it had earlier and take that of my single spreadsheet? Thanks for any clues. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain formatting of Excel sheet when copying data into it
Try using Paste Special - Text in instead of plain paste.
-- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- wrote in message ... How do I copy data from different sources (in different fonts) into my spreadsheet and make sure my spreadsheet retains its own font i.e. the incoming data should lose the font it had earlier and take that of my single spreadsheet? Thanks for any clues. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain formatting of Excel sheet when copying data into it
Thanks for replying Earl. Yes this works. However, actually there is
already a worksheet I have that someone created but is no longer with the company into which when I copy data, the original formatting of the data is lost. That is what I am trying to replicate (without having to remember to use Paste Special). Any ideas? I can e-mail the worksheet to anyone interested if needed. Thanks so much. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain formatting of Excel sheet when copying data into it
Carmela,
If the paste special works as you want, then you could have a macro do the paste special, instead of using paste or paste special. Other'n that, the regular paste will continue to overwrite the cell formatting with that of the copied data. For the macro solution, copy/paste this into a module in the workbook (or into Personal.xls or any other workbook) (which must be open while you want to use it): Sub PasteSpecialText() ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False End Sub Using Tools - Macro - Macros, you can assign a shortcut key to facilitate doing the paste special, like Ctrl-Shift-q, or something like that. Magic -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- wrote in message ... Thanks for replying Earl. Yes this works. However, actually there is already a worksheet I have that someone created but is no longer with the company into which when I copy data, the original formatting of the data is lost. That is what I am trying to replicate (without having to remember to use Paste Special). Any ideas? I can e-mail the worksheet to anyone interested if needed. Thanks so much. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain formatting of Excel sheet when copying data into it
No need to send a workbook to anyone.
We've all been there. This is the Excel default function and you would need VBA code to copy and paste without pulling the formatting from source range. OR remember to Paste SpecialValues as Earl suggests, which is all your macro would do. Sheet event code you could use in the target sheet. Private Sub Worksheet_Change(ByVal Target As Range) 'retain formatting when a cell is copied over Dim myValue With Application .EnableEvents = False myValue = Target.Value .Undo Target = myValue .EnableEvents = True End With End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that sheet module. Alt + q to return to the Excel window. Copy a range of formatted data from source sheet. Paste to Target sheet and target formatting is retained. Gord Dibben MS Excel MVP On Tue, 26 Feb 2008 12:54:08 -0800 (PST), wrote: Thanks for replying Earl. Yes this works. However, actually there is already a worksheet I have that someone created but is no longer with the company into which when I copy data, the original formatting of the data is lost. That is what I am trying to replicate (without having to remember to use Paste Special). Any ideas? I can e-mail the worksheet to anyone interested if needed. Thanks so much. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retain formatting of Excel sheet when copying data into it
Beautiful! Works like a charm. It's awesome to have smart people like
you to help. Thanks a ton. Carmela |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy/paste want to retain cell coloration but lose the conditional formatting source data | Excel Discussion (Misc queries) | |||
Stop excel from copying data from one sheet to another | New Users to Excel | |||
How do I merge from Excel into Word doc and retain formatting? | Excel Discussion (Misc queries) | |||
Retain PivotTable formatting after "Refresh Data" | Excel Discussion (Misc queries) | |||
How do I copy and paste Excel charts and retain formatting? Mine . | Charts and Charting in Excel |