Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
coping of cell using dragging the cell downward or rightward | Excel Discussion (Misc queries) | |||
Coping part of a cell content into a seperate cell | Excel Discussion (Misc queries) | |||
Coping a Coloured cell | Excel Worksheet Functions | |||
coping formulas from one cell to another | Setting up and Configuration of Excel | |||
coping a formulas to another cell. | Excel Worksheet Functions |