I want paste / fill to NOT copy cell formatting

[TDS_Eric]

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
2003???

 

[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

 

[TDS_Eric]

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
again.

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

Thanks again.

 

[Gord Dibben]

Without a macro you're stuck with the Paste Special>Formulas

Have a llok at John Walkenbach's site for an Undo routine.

http://www.j-walk.com/ss/excel/tips/tip23.htm


Gord