Copying Formats Quickly with VBA

[mmccabe]

I have a macro that copys the formats of several adjacent cells (A50:J50) and then pates these formats to the rows above (A2:J49). Functionally, the macro works fine, but the routine is a little slower than I would like it to be--and I need to perform this routine quite frequently. Is there a different method that I can use to speed this up?

For example I know how to copy values in VBA without selecting, copying and pasting to such as...

Range("A2:J49").Value = Range("A50:J50").Value
(much faster than "select", "copy", "paste")

Can I substitute the word "Value" in the command above with something else that will copy ALL formating (colors, borders, fonts, conditionals) to the specified locations? Any ideas?

Your help is greatly appreciated.

Mike

 

[Oaktree]

I think you have to do:

Range("A2:J49").Copy
Range("A50:J50").PasteSpecial Paste:=xlPasteFormats

 

[mmccabe]

Thanks for responding. The method in your post is exactly what I am using currently. Are you saying that this may be the only way?