Coping Values - Not the cell

[Tim]

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.

 

[Bob Phillips]

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

 

[Steve Smallman]

As below, after your initialisation of the routine (any DIMs or SETs)
add an error trap to ensure that every time you exit the routine, you ensure
you turn screenupdating on

On error goto errortrap
application.screenupdating =false
code
code
more code

exit sub

errortrap:
msgbox error & error$
application.screenupdating=true
end sub

now, the method you are using to copy and paste is valid, but to achieve
what you want, you would be better to use the .copy method followed by the
..pastespecial method. When using pastespecial you can elect to paste values

run a quick macro record to get the syntax for the two commands.

This should fix your issue.

Steve

include the screenupdating.false line

 

[Tim]

Thanks for the help!

As below, after your initialisation of the routine (any DIMs or SETs)
add an error trap to ensure that every time you exit the routine, you ensure
you turn screenupdating on

On error goto errortrap
application.screenupdating =false
code
code
more code

exit sub

errortrap:
msgbox error & error$
application.screenupdating=true
end sub

now, the method you are using to copy and paste is valid, but to achieve
what you want, you would be better to use the .copy method followed by the
.pastespecial method. When using pastespecial you can elect to paste values

run a quick macro record to get the syntax for the two commands.

This should fix your issue.

Steve

include the screenupdating.false line