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.