Serious Slowness in this Situation--EnableEvents apparently not the issue

[Arnold]

Good Day,

Posts here said to use Application.EnableEvents.False at the start of
certain routines and Application.EnableEvents.True at the end to
expedite the routines. However, the use of Application.EnableEvents,
as well as Application.ScreenUpdating, is not speeding up my code

I have a worksheet named 'Students' that has a bit of worksheet_change
code---includes multiple instances of Application.EnableEvents true
and false depending on conditions. The code runs well when records
are being added or changed in that sheet.

There is a separate module that copies columns from that sheet, pastes
them into another sheet, grabs data from yet another sheet, selects
all the new data, then pastes (values only) back into the 'Students'
sheet. During the data transfer, the status bar says Calculating
100%, but then takes anywhere from 2 minutes on a fast computer to 15
minutes on a slow computer to complete the process.

I've also tried to use Application.Calculation = xlManual and
xlAutomatic; no difference in speed. Questions:

Does Application.EnableEvents.False in a module that writes data into
a sheet with change events disable all of the change events on that
worksheet, or are the worksheet change events firing when the data is
being put into the sheet?

Is there a way to turn off the entire worksheet change event while the
module is running?

Is the slowness due to the copying & pasting back & forth? But there
will only be 120 to 160 records total, and only 16 columns being
manipulated.

Any insight would be greatly appreciated.
Arnold

 

[Bob Phillips]

Enableevents= false will turn off events, so this will stop other events,
such as calculation firing. That is fine when using the event procedures,
and can be useful when using a batch process, which I assume that your
copy/paste macro is, if you are working on cells that are being monitored
within an event procedure. Similarly, turning calculation off will help that
batch process, as does screenupdating.

After that,. I would guess it is a matter of looking at the code, and seeing
if savings can be made by better coding techniques. Can you paste that
copy/paste routine here for review?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arnold]

Hi Bob,

Thanks; I just re-worked it so as to not have to use the temp sheet
and the code works faster now. Thanks for your consideration.

Arnold