Paste SpecialValues will paste just the data without overwriting any
formatting.

There is a Paste SpecialValues button you can place on your Toolbar.

ToolsCustomizeEdit.......scroll down until you see a paste icon with the
number 12 on it. Drag to your Toolbar.

Or you could place event code in the target
worksheet that preserves the formatting when anything is copied into it.

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

This is sheet event code. Right-click on the target sheet tab and "View Code".

Copy/paste into that sheet module. Alt + q to return to the Excel sheet window.

You can now copy from source sheet and paste to target sheet with no disruption
of target sheet formatting.


Gord Dibben MS Excel MVP

On Wed, 2 Jan 2008 14:24:03 -0800, smccarvi
wrote:

I often am able to set up my excel tables in advance - Borders, bold or
italic cells, etc. and then later when the data is available I often am
copying and pasting values from other worksheets and formulas from with in my
own sheet. Unfortuntly when pasting I lose my formatting.

Is it possible to lock-in the formatting at some point before I begin
putting in data? It would save me a huge amount of time.