I am working on a macro that updates data from an old template to a
new template. I am coping the data I need by going sheet-by-sheet and
cell-by-cell. The code I am using to do this looks like this:

'Copy Company name
Workbooks(OldFileName).Sheets("Company") _
.Range("F9:I16").Copy Workbooks(NewFileName) _
.Sheets("Company").Range("F9:I16")

'Copy contact name #1 info
Workbooks(OldFileName).Sheets("Company") _
.Range("F20:I22").Copy Workbooks(NewFileName) _
.Sheets("Company").Range("F20:I22")


Two questions.

1. I use Application.ScreenUpdating = True/False and
Application.DisplayAlerts = True/False before and after this code but
I can still see Excel's screen change when switching files and sheets.
What do I need to do for this not to happen?

2. As I was working on the code I started thinking that "Copy" may not
be the best way to do this. I my have changed the format of a cell in
the new template to correct something in the old template and when I
use "Copy", the old cell and its formats are copied into the new
template. Is there a better way of doing this outside of assigning
each cell I wish to copy into the new template to a variable, and then
assigning the value of the variable to a cell in the new template?
(If I must assign a variable as previously mentioned, how do I write
the code so that it all is on the same command line as in the code
above? (I was told it runs faster that way.)

Thank you for you help.

Tim,

How about pasting just the values like so

Workbooks(OldFileName).Sheets("Company") _
.Range("F9:I16").Copy

Workbooks(NewFileName) _
.Sheets("Company").Range("F9:I16").PasteSpecial Paste:=xlValues


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Tim" wrote in message
om...
I am working on a macro that updates data from an old template to a
new template. I am coping the data I need by going sheet-by-sheet and
cell-by-cell. The code I am using to do this looks like this:

'Copy Company name
Workbooks(OldFileName).Sheets("Company") _
.Range("F9:I16").Copy Workbooks(NewFileName) _
.Sheets("Company").Range("F9:I16")

'Copy contact name #1 info
Workbooks(OldFileName).Sheets("Company") _
.Range("F20:I22").Copy Workbooks(NewFileName) _
.Sheets("Company").Range("F20:I22")


Two questions.

1. I use Application.ScreenUpdating = True/False and
Application.DisplayAlerts = True/False before and after this code but
I can still see Excel's screen change when switching files and sheets.
What do I need to do for this not to happen?

2. As I was working on the code I started thinking that "Copy" may not
be the best way to do this. I my have changed the format of a cell in
the new template to correct something in the old template and when I
use "Copy", the old cell and its formats are copied into the new
template. Is there a better way of doing this outside of assigning
each cell I wish to copy into the new template to a variable, and then
assigning the value of the variable to a cell in the new template?
(If I must assign a variable as previously mentioned, how do I write
the code so that it all is on the same command line as in the code
above? (I was told it runs faster that way.)

Thank you for you help.