Is there a VBA PasteSpecial(PasteAllExceptFormats) ?

  • Thread starter Thread starter Richard Buttrey
  • Start date Start date
R

Richard Buttrey

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
__________________________
 
Sorry... there is nothing in a single line. You are probably bet with a
regular paste and then put back the colours...
 
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
(e-mail address removed)
 
Sub copyAndPasteSpecial()
Dim rng As Range
Set rng = Range("A1:G100")
rng.Copy
Range("H1").PasteSpecial xlPasteFormulasAndNumberFormats
End Sub
Click to expand...


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
__________________________
 
Back
Top