Is there a VBA PasteSpecial(PasteAllExceptFormats) ?
 

I'd like to be able to copy a column which includes formulae, values
and cell background colour formatting, and paste it to another column
which has different colour backgrounds.

I need to keep the formulae from the copied column and the formatting
for the object column.

I realise I can do a PasteAll, and then reset the original colours
with another line or two of code, but a single operation would be
preferable.

Any suggestions please?

Usual TIA




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Is there a VBA PasteSpecial(PasteAllExceptFormats) ?
 

Sorry... there is nothing in a single line. You are probably bet with a
regular paste and then put back the colours...
--
HTH...

Jim Thomlinson


"Richard Buttrey" wrote:

I'd like to be able to copy a column which includes formulae, values
and cell background colour formatting, and paste it to another column
which has different colour backgrounds.

I need to keep the formulae from the copied column and the formatting
for the object column.

I realise I can do a PasteAll, and then reset the original colours
with another line or two of code, but a single operation would be
preferable.

Any suggestions please?

Usual TIA




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Is there a VBA PasteSpecial(PasteAllExceptFormats) ?
 

Richard

Would this work. It just pastes formulas and number formats (Constants will
copy too)

Sub copyAndPasteSpecial()
Dim rng As Range
Set rng = Range("A1:G100")
rng.Copy
Range("H1").PasteSpecial xlPasteFormulasAndNumberFormats
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Richard Buttrey" wrote in
message ...
I'd like to be able to copy a column which includes formulae, values
and cell background colour formatting, and paste it to another column
which has different colour backgrounds.

I need to keep the formulae from the copied column and the formatting
for the object column.

I realise I can do a PasteAll, and then reset the original colours
with another line or two of code, but a single operation would be
preferable.

Any suggestions please?

Usual TIA




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Is there a VBA PasteSpecial(PasteAllExceptFormats) ?
 

On Wed, 12 Jul 2006 22:44:45 +0100, "Nick Hodge"
wrote:

Sub copyAndPasteSpecial()
Dim rng As Range
Set rng = Range("A1:G100")
rng.Copy
Range("H1").PasteSpecial xlPasteFormulasAndNumberFormats
End Sub


Excellent. Thanks Nick, just what I wanted.

It's funny, but I must have seen that prompted option hundreds of
times and it's never really registered with me before.

Many thanks,


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Is there a VBA PasteSpecial(PasteAllExceptFormats) ?
 

No problem

I get that kind of blindness all the time <g

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Richard Buttrey" wrote in
message ...
On Wed, 12 Jul 2006 22:44:45 +0100, "Nick Hodge"
wrote:

Sub copyAndPasteSpecial()
Dim rng As Range
Set rng = Range("A1:G100")
rng.Copy
Range("H1").PasteSpecial xlPasteFormulasAndNumberFormats
End Sub


Excellent. Thanks Nick, just what I wanted.

It's funny, but I must have seen that prompted option hundreds of
times and it's never really registered with me before.

Many thanks,


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________