VBA code execution time

Hi

I'm hoping some one might be able to review this code and offer some ideas why it can take so long to run in Excel 2016.

Its a very basic copy and paste set of instructions that copies data from multiple different tabs and paste it into a single tab.

Thing is sometimes it takes a few seconds to run, and other times it can take 15 minutes. Is there anything you experts can see that I could amend to make it run more consistently?

Thanks in Advance.

Please Login or Register  to view this content.

Without knowing more specifics it's hard to give definitive answer but always a good idea to optimise your environment, and avoid native copies etc {in my experience!}

The below is a quick update on your code - including some environment settings, and tmpArray use etc...

Please Login or Register  to view this content.

I'd suggest replacing reference to ActiveSheet with the Sheets("name") reference, unless it's meant to vary on activation of routine.

On a final aside, I'm assuming (given explicit reference to row 2500) that you have lots of blanks in these worksheets, else you will end up overwriting data generated on previous worksheet iterations.

If the above is true you can likely use a different construct to identify populated area on your precedent worksheet(s) as opposed to fixing on a hard coded range.

Hi

Thanks for the reply, and the code you suggested runs a lot faster. Although for reasons I am not sure of, stops coping data at Thursday and terminates with "Computer Says No". There is nothing different about the data from Thursday onwards.

The data itself is a just basic numeric data and a couple of columns that contain basic formulas. It comes in daily and never really longer than 400 to 500 rows, therefore the copy range doesn't need to be more than that.

Error is;

1004
Application-defined or object-defined error

1239 lines of data are copied, which is exactly Monday to Wednesday

It's strange, as soon as I hit run the error message appears, but the code runs anyway.
As for the data, Thursday data is identical in format and rough size.

Hi

Uploading was not an option for privacy reasons, however I was able to experiment and find that simply adding the code to turn events on and off had a massive impact, and reduced the copy time down to seconds.

I am still curious as to why the code example stopped at Thursday, but I have achieved the desired results regardless.

Thanks to everyone that helped and rep has been added.