Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
Hello:
I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
The two biggest culprets in terms of slow code are recussive calls and
caculation settings. I am guessing that you are not getting into recursive calls based on what you are doing (usually based on code execution of a change event). Try setting calculations to manual application.calculation = xlManual 'at the beginning of the procedure application.calculation = xlAutomatic 'at the end of the procedure HTH "Marty" wrote: Hello: I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
Hi, Jim:
Thanks for the suggestion, but it didn't seem to help. I thought about turning off screen updating (and turning it back on at the end), but the only thing on the screen that gets updated is my counter (workbooks A and B are open, but they're not on the screen. Does screen updating only affect the active workbook? MARTY "Jim Thomlinson" wrote: The two biggest culprets in terms of slow code are recussive calls and caculation settings. I am guessing that you are not getting into recursive calls based on what you are doing (usually based on code execution of a change event). Try setting calculations to manual application.calculation = xlManual 'at the beginning of the procedure application.calculation = xlAutomatic 'at the end of the procedure HTH "Marty" wrote: Hello: I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
Hi Marty,
Are there any alternatives to copying the data in single-row increments? Maybe you could apply a filter to the original data based on some criteria and then copy/paste in bulk? -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Marty wrote: Hello: I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
Using a counter and displaying % complete on the status
bar can also greatly reduce the speed at which your macro executes. Try breaking up the display of the counter into multiples of 1000 or 10000. MVP Jan Karel Pieterse talks about it he http://www.dicks-blog.com/archives/2...5/performance- hit-of-statusbar/ HTH Jason Atlanta, GA -----Original Message----- Hello: I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
Try looking in the task manager while the code is executing to see how much
memory you are using up. Other than that post your code and we can see if there is anything that sticks out as a problem... "Marty" wrote: Hi, Jim: Thanks for the suggestion, but it didn't seem to help. I thought about turning off screen updating (and turning it back on at the end), but the only thing on the screen that gets updated is my counter (workbooks A and B are open, but they're not on the screen. Does screen updating only affect the active workbook? MARTY "Jim Thomlinson" wrote: The two biggest culprets in terms of slow code are recussive calls and caculation settings. I am guessing that you are not getting into recursive calls based on what you are doing (usually based on code execution of a change event). Try setting calculations to manual application.calculation = xlManual 'at the beginning of the procedure application.calculation = xlAutomatic 'at the end of the procedure HTH "Marty" wrote: Hello: I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loss of Speed During Copy Operation
Jake:
A most generous offer, but I can't. The workbook contains proprietary/business confidential information and I can't share it outside the company. Thank you very much anyway. MARTY "Jake Marx" wrote: Marty, I'd be willing to take a look at the workbook & code if you email it to me: . -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Marty wrote: Jake: Unfortunately, no. The B worksheet is an expanded version of A, and I'm not able to to a bulk copy and paste. Thanks anyway, MARTY "Jake Marx" wrote: Hi Marty, Are there any alternatives to copying the data in single-row increments? Maybe you could apply a filter to the original data based on some criteria and then copy/paste in bulk? -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Marty wrote: Hello: I'm using Excel 2003. I've written a utility to transfer selected data from a 10,000 row by 20 column sheet (A) into a second sheet (B). I want to maintain any format additions (cell background color, font changes, cell comments, etc.) made by the user in sheet A, so I'm using this type of statement in the code: A.Cells(RO, COL).Copy Destination:=B.Cells(NewRO, NewCOL) I have a counter programmed onto the screen to keep track of the row the utility is working on. HERE IS THE PROBLEM: It starts out fast and then slows way down. I would like to know if there is a way to maintain the speed throughout execution of the code. It seems like there's a memory buffer which gets full (or something). Any suggestions? Thanks in advance, MARTY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hardship letter explaining devastating loss property loss financi | Excel Discussion (Misc queries) | |||
loss of speed with excell 2007 | Excel Discussion (Misc queries) | |||
Help: Data Loss, have prev. copy of sheet | Excel Discussion (Misc queries) | |||
"ignoring" hidden cells in a copy/paste operation | Excel Discussion (Misc queries) | |||
What of these commands ruin my copy - paste operation between workbooks? | Excel Programming |