Coping Values - Not the cell
 

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.

Coping Values - Not the cell
 

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.

Coping Values - Not the cell
 

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
"pancho" wrote in message
...
1.-
Application.ScreenUpdating = False
is enough to don't see the changes on the screen. be sure
you don't use
Application.ScreenUpdating = True
till the end of your macro (each TRUE command you issue
updates the screen and enable the updating, so be sure you
don't have a TRUE command till the end)
2.- try this if you only need to copy VALUES.
Workbooks(NewFileName).Sheets("Company"). _
Range("F9:I16").Value=
Workbooks(OldFileName).Sheets("Company"). _
Range("F9:I16").Value



Francisco Mariscal
fcomariscal at hotmail dot com



-----Original Message-----
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.
.

Coping Values - Not the cell
 

Thanks for the help!




"Steve Smallman" wrote in message ...
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
"pancho" wrote in message
...
1.-
Application.ScreenUpdating = False
is enough to don't see the changes on the screen. be sure
you don't use
Application.ScreenUpdating = True
till the end of your macro (each TRUE command you issue
updates the screen and enable the updating, so be sure you
don't have a TRUE command till the end)
2.- try this if you only need to copy VALUES.
Workbooks(NewFileName).Sheets("Company"). _
Range("F9:I16").Value=
Workbooks(OldFileName).Sheets("Company"). _
Range("F9:I16").Value



Francisco Mariscal
fcomariscal at hotmail dot com



-----Original Message-----
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.
.