Unexplained Macro Lag between different Spreadsheets(Reports) for same code

[SamHink123]

I have a couple sets of virtually the same macro to send blast emails from a report created by an ERP system.

There are 4 different ERP systems utilized to create 4 different reports with the same general information. Some have more detail than others as well as different styles of formatting.

The first 2 ran smoothly, averaging about 3 seconds per email. Each report had about 4000 lines of invoice data that were sent through 750-800 emails. Each file was about 4500 KB in size.

But, when I got to the third ERP system, my below code took forever to run through the initial formatting section, and would then send an email about once a minute. This ERP Report had over 10,000 lines of invoice data. This report had more formatting than the previous two as well as some additional columns of information to be removed. The file size was about 9-10,000 KB in size.

When I exit the macro after a few minutes of it running slowly, the code in Bold/Red below is commonly where the debugger will cause problems. (I've also attached a sample of the workbook I'm using and replaced any sensitive information with Generic info or symbols to signify their input values)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I got around this by inserting a new worksheet on the same workbook, copy/pasting about half of the report into the new sheet and then running the code, then once completed I'd copy in the other half and run the macro again. I didn't change/add/remove any formatting and it ran just as quick as the prior two reports had.

http://www.excelforum.com/attachment...1&d=1464129274

Does anyone have any ideas on what could be causing this?

[SamHink123]

If I remove formatting, do you think this would solve my issue?

[TMS]

Possibly; you're putting borders round millions of cells. Do you really have 10,000 rows of invoicing data? Maybe use Ctrl-End to see where Excel thinks the last row of data is. If it's way further down than the actual data, that's more likely your problem. 10 Mb is quite a big file and likely to be sluggish. When you copy half the data, twice, and it performs ok, that's indicative that you're leaving something behind. You might find that you can copy ALL the data and it performs ok.