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

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

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

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

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
.

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

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