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