I want paste / fill to NOT copy cell formatting



When you use either copy/paste or fill to copy data, the cell formatting also
gets copied. I don't mind the number formats being used, but it is extremely
annoying that other formatting such as boarders and shading gets copied too.

I have my formatting done the way I need it, but at times I need to update
the data. I do this using paste or fill. Then my formatting gets all messed
up and I spend all kinds of time fixing it.

I know I can use Paste special to just paste the data but this is a pain to
have to use every time I paste. I want Paste Special/Formulas Only to be the
Default behaviour for paste. How can I make this happen? The same for the
Fill command. I want it by default to only fill formulas, not formatting.

Is it just me, or is this different in Office 2007 than it was in Office



Gord Dibben

Excel 2003 default is not "Formulas" so there is no difference between versions.

How about a macro assigned to a button on a Toolbar or whereever they get placed
in 2007?

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

Note: only works with contiguous copy range.

Gord Dibben MS Excel MVP


Thanks. I can create a macro that activates the Paste Special command. It
does work.
But, there are two problems.

First, the undo command is unavailable after using the macro. So, in other
words if I paste something in the wrong place by accident or whatever, I
don't have an undo option. That is pretty bad.

Second, it doesn't take care of the Fill command. I could create a macro
that does basically the same thing, but then I run into the undo problem

Is there no way to lock the formatting? Using the "Protect sheet" stuff
doesn't work for many reasons.

Thanks again.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question