Is it possible to lock-in or freeze formatting?

[smccarvi]

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.

 

[Gord Dibben]

Paste Special>Values will paste just the data without overwriting any
formatting.

There is a Paste Special>Values button you can place on your Toolbar.

Tools>Customize>Edit.......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

 

[smccarvi]

After trying out the code this week, it doesn’t quite do what I need. One
problem is that it always pastes values when I often need to paste formulas;
another is that I can no longer "undo" which I need because I make a lot of
mistakes.

I noticed that after pasting a clipboard appears with an option to "Match
Destination Formatting". Could the code be modified to always have anything
that is pasted match the destination format?

 

[Gord Dibben]

I don't know how to access and change the default of the Paste Options button.

You may have to go to a macro rather than the sheet event code.

This should do what you want but only if the copied range is-contiguous.

Sub copy_no_change()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Selection
Set rng2 = Application.InputBox(prompt:= _
"Select Any Cell to paste to", Type:=8)
rng2.Resize(rng1.Rows.Count, rng1.Columns.Count).Formula _
= rng1.Formula
End Sub


Gord