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

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

On Thu, 10 Jan 2008 13:56:02 -0800, TDS_Eric
wrote:

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

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" wrote:

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

On Thu, 10 Jan 2008 13:56:02 -0800, TDS_Eric
wrote:

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

Without a macro you're stuck with the Paste SpecialFormulas

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

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


Gord

On Thu, 10 Jan 2008 17:00:23 -0800, TDS_Eric
wrote:

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" wrote:

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

On Thu, 10 Jan 2008 13:56:02 -0800, TDS_Eric
wrote:

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