Can anyone speak to the performance issues between copying and pasting a
range of formats vs programmatically setting formats? It seems that it is
much faster to copy and paste, than looping through a range, and I am
wondering if anyone has any input on this.

We currently keep a template sheet, but I would like to move away from this.

Thanks in advance.

Regards,
Adam

Hi Adam,

A couple of notes:
1. try applying your formatting in blocks so you are not applying it to each
cell.
2. have you turned of screenupdating? Application.ScreenUpdating = False
- you need to set it back to True at the end of your procedure(s)

"Adam" wrote:

Can anyone speak to the performance issues between copying and pasting a
range of formats vs programmatically setting formats? It seems that it is
much faster to copy and paste, than looping through a range, and I am
wondering if anyone has any input on this.

We currently keep a template sheet, but I would like to move away from this.

Thanks in advance.

Regards,
Adam

Unfortunately we did as much as we could to use ranges, and screen updating,
and yet it seems that copying still seems to out paces the programatic.

Any other thoughts?

"Simon Shaw" wrote:

Hi Adam,

A couple of notes:
1. try applying your formatting in blocks so you are not applying it to each
cell.
2. have you turned of screenupdating? Application.ScreenUpdating = False
- you need to set it back to True at the end of your procedure(s)

"Adam" wrote:

Can anyone speak to the performance issues between copying and pasting a
range of formats vs programmatically setting formats? It seems that it is
much faster to copy and paste, than looping through a range, and I am
wondering if anyone has any input on this.

We currently keep a template sheet, but I would like to move away from this.

Thanks in advance.

Regards,
Adam

Looks like you have your answer.

--
Regards,
Tom Ogilvy


"Adam" wrote in message
...
Unfortunately we did as much as we could to use ranges, and screen
updating,
and yet it seems that copying still seems to out paces the programatic.

Any other thoughts?

"Simon Shaw" wrote:

Hi Adam,

A couple of notes:
1. try applying your formatting in blocks so you are not applying it to
each
cell.
2. have you turned of screenupdating? Application.ScreenUpdating = False
- you need to set it back to True at the end of your procedure(s)

"Adam" wrote:

Can anyone speak to the performance issues between copying and pasting
a
range of formats vs programmatically setting formats? It seems that
it is
much faster to copy and paste, than looping through a range, and I am
wondering if anyone has any input on this.

We currently keep a template sheet, but I would like to move away from
this.

Thanks in advance.

Regards,
Adam

I often use the following two procedures at the begining and ending of
formatting and sheet changing procedures that run slow:

'---------------------------
Sub Start_Sub()

With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlManual
End With
ActiveSheet.DisplayAutomaticPageBreaks = False

End Sub
'---------------------------
Sub End_Sub()

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
'-------------------------

you can improve them for say Calculation and DisplayAutomaticPageBreaks to
store the existing state in a variable, then set it back in the end_sub
procedure.

Simon

"Adam" wrote:

Unfortunately we did as much as we could to use ranges, and screen updating,
and yet it seems that copying still seems to out paces the programatic.

Any other thoughts?

"Simon Shaw" wrote:

Hi Adam,

A couple of notes:
1. try applying your formatting in blocks so you are not applying it to each
cell.
2. have you turned of screenupdating? Application.ScreenUpdating = False
- you need to set it back to True at the end of your procedure(s)

"Adam" wrote:

Can anyone speak to the performance issues between copying and pasting a
range of formats vs programmatically setting formats? It seems that it is
much faster to copy and paste, than looping through a range, and I am
wondering if anyone has any input on this.

We currently keep a template sheet, but I would like to move away from this.

Thanks in advance.

Regards,
Adam