Macros run slow after loading another workbook, and fast if I re-open Excel?

Apologies if this has been answered previously - I couldn't think of a search term that wouldn't bring up a large number of results!

This is a document I'm developing at work, so unfortunately I cannot upload it for people to test with. I could provide blanked out screenshots if required, but I hope this won't be necessary.

I have an Excel file (I'll refer to as the 'master file'), which I use to build up a report (consisting of rows of data). It has quite an extensive front panel which utilises a large number of macros. The master document is ~6 MB in size and has ~300 worksheets (most of which are back-end and xlveryhidden).

Colleagues will be using the master file to generate reports. They may want to send the data to other people, and so to assist with this, I have developed an import/export feature, which saves a new document which only contains the data (none of the macros, front panel, etc.). Not only does this mean that there is less risk of the master document getting into the wrong hands, but it also means that the file they send is significantly reduced in filesize (~200 KB rather than 6 MB), as it only contains the bare essentials.

So, that's a bit of the background, here comes the problem...

I have recently discovered an issue with the master file. When I import data using my own import feature, it opens the small Excel file that contains the data, copies the data into the master file, then closes the small Excel file. Once this has completed, however, the macros in my document run significantly slower (often 5-10 times slower than normal). Given that some of these macros take a few seconds to run at the best of times, this can be unbearably slow.

My first thought was that it was running slower because it was having to calculate the extra cells associated with the imported data, however, I found that if I removed all the data after importing, it would still run significantly slower than normal.

I have found one solution, but it is by no means ideal. If I import the data, save the master file with the data imported, close Excel, and re-open the master file, the macros run reasonably fast - not quite as fast as before the data is imported, but significantly faster than if I had imported and not saved/closed.

I tried searching online for a solution to this problem, but couldn't find anything concrete. Through my search, I read (here) that "Excel does not release all memory when workbooks are closed". Would I be right in thinking this is the likely cause of the slowdown in macro runtime? If it is, is there a possible solution that does not involve me having to save and close Excel then re-open the file?

A key reason for the import/export feature was so that the user doesn't have to save the data in the master file, so ideally I would like to avoid using this as a solution.

Any help would be greatly appreciated. If you need more information, please let me know!

Hi everyone

Actually I am facing a similar issue, the macro runs perfectly fine, and then suddenly in takes upto 2 minutes (when before it was at 1 second)
Used Range does not change, so seems to be a similar issue as for Pedsdude

Norie,

It's a rather extensive user interface with a large amount of VBA coding behind it. Unfortunately I'd like to not post the code because (a) there's so much of it that I doubt it would be of any use unless someone was willing to spend many hours understanding what it does and how it all interacts, and (b) it's something I'm working on as part of my job and much of it is confidential.

I was more hoping there would be a fundamental issue / cause here that may be apparent to someone who knows more about Excel than I do. I don't think it's the macros themselves that are causing the issue here, as the same macros run at different speeds before and after re-opening the document. It seems to me that Excel itself is becoming sluggish in general following the import, rather than data causing the macros to slow down.

Unfortunately I do need 300 worksheets - they contain product information and different values are shown on the front page depending on the options selected. I could potentially combine some of these worksheets, but it would make updating them more labour intensive. The user never sees these worksheets and they do not contain too much data, so I don't think it is too much of an issue. I've been making frequent backups of the file during development, so I don't believe I'm at risk of losing any data as a result of it becoming corrupt.