Microsoft Excel - Is it possible to copy formats without using paste special?

Hi Guys,

 

Quick question and I’m not sure if this can be done without too much faffing.

 

When programming in Excel, I don’t like to use the activate, select, copy & paste methods as they tend to slow things down, especially when dealing with large amounts of data between sheets / workbooks.  I prefer to use arrays to move information around as it’s much neater and quicker.

 

That being said, I’m stumped when it comes to copying cell formats.  Is there anyway I can make one range of cells look like another (e.g. Border styles inside & out, interior colour etc), without having to use paste special formats.

 

I could format all of this through code, but there are several different formats and it would be painstaking to write all this up.

 

Any ideas or should I just stick to paste special?

 

Thanks

 

Pete

Hi
I'm not sure if it's what you want but anyway.
The first line "Copy=Destination"  will copy the format and formula
'Cells(i, 1).Resize(1, 4).Copy Destination:=fnl.Cells(lastrow + 1, 1) 
The line below will copy only the value
fnl.Cells(lastrow + 1, 1).Resize(1, 4).Value = Range("A" & i & ":" & "D" & i).Value

Thanks for this John,

Unfortunately the copy method will paste the values over my new data, so I can't use this.

It's not the end of the world, I'm just trying to see if there's any way I can make my coding more efficient.

Cheers

Pete