Retain formatting of Excel sheet when copying data into it

[carmela_wong]

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.

 

[Earl Kiosterud]

Try using Paste Special - Text in instead of plain paste.

 

[carmela_wong]

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.

 

[Earl Kiosterud]

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

 

[Gord Dibben]

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 Special>Values 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

 

[carmela_wong]

Beautiful! Works like a charm. It's awesome to have smart people like
you to help.

Thanks a ton.
Carmela