Hangs when saving spreadsheet using macro

I currently have a spreadsheet which every morning runs a macro. This used to run perfectly and quickly, but in the last month or so, it's started playing up during the saving phase and I don't know why.
To explain better, I should explain what the spreadsheet and macro do.
Basically, it is an excel spreadsheet which open on a PC that I remote desktop to that contains an Excel Table which links to our database every morning to refresh, this table shows our multiple clients with several lines per client. This works fine and dandy.
The aim of the macro is to create a separate work book for each client called by that client name. There are around 500 clients which makes in unmanageable to do manually and the clients are not always the same.
The macro creates a second table on sheet 2, and this table gives the first and last instance of the client name, this in turn helps identify which rows the macro copies and then pastes into a new workbook. The final part is to save the newly created workbook with the client name, then the loop kicks in and it continues to the next client until they are all done.
The problem itself as I said before is during the saving phase of the new workbook. For about 99% of the workbooks they save fine, but for about 3 or 4 whilst it’s saving, it hangs when you have the option to cancel the saving, (even though it has actually saved) and this stops the whole process. this only happens when using the VB script to open the spreadsheet and run the macro.
Without the problem this takes about 15-20 minutes, with the problem it takes until someone clicks cancel on the bit where it gets stuck.
Any help would be appreciated. (I have attached an example spreadsheet and the code below)

Please Login or Register  to view this content.

Wow thanks for looking it and gving such a detailed reply!

You're right about the not needing to select te cells, and that was just me being lazy and not removing them.

I'm excited about it working in about a minute! But I've tried using your code and I need to test it further as it was just copying over the whole table into new work sheet and calling it that. (So the whole table was copied over and the first customer was used for the file name every time. Perplexing...

Thanks for your help so far though...

Apologies, I didn't realise that might be a problem.

I have included a desensitised version...

This is essentially the file, with data changed. The data is within a table which links to MS Query to query our database...

Good luck if you do get a chance to have a look at this...and thanks for your help again.

Attached is a zip file with 2007 and 2003 version of the file, I use the 2007 version...

Thanks

Gah! I don't know why it isn't working then...I just tried i again and I'm getting nowhere. Disappointing..!

Thanks for your help JB. You are have been a star!

I would like to bump this thread- as helpful as JBeaucaire has been for the code, he did not answer the question. I am having the same trouble, but I don't know if it's because I'm saving to a server vs. locally. If anyone has further information on the question, it would be much appreciated.

Just to clarify his question, occasionally whilst saving, Excel will display the alert "Saving" with the prompt "Saving as: '<file save location>'" with the only option being a button labeled "Cancel." When you press the cancel button, the file still saves properly, but for some odd reason Excel stops the macro and gets stuck on this window until you press the button. Is there any reason why it would do this and/or a way to prevent it?