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!