Loss of Speed During Copy Operation

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Jake Marx]

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]

 

[Jason Morin]

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 here:

http://www.dicks-blog.com/archives/2005/01/05/performance-
hit-of-statusbar/

HTH
Jason
Atlanta, GA

 

[Guest]

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

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]

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

 

[Guest]

Jason:

Thanks for the response. I need to clarify: I don't actually have a
"counter" as I believe you're thinking. I merely have a cell in the utility
where I populate the number of the row from which the data is being copied,
e.g.:

UTIL.Cells(1,1) = Ro

Sorry for the confusion.

I don't see how this could be the source of the slowdown. Again, it starts
out very quickly and then the speed steadily decreases, as if some sort of
memory buffer is being clogged.

Any other thoughts?

MARTY

 

[Guest]

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

 

[Jake Marx]

Marty,

I'd be willing to take a look at the workbook & code if you email it to me:
(e-mail address removed).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

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

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]

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

 

[Guest]

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

Marty,

I'd be willing to take a look at the workbook & code if you email it to me:
(e-mail address removed).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

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

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