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.

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.

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.

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.

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.

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