VBA Save Code Intermittment Hanging

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to perform the following:

• Loop through a specifc set of sheets in my workbook,

• Copy them,

• Then create a new workbook, pasting the saved sheet into this, then

• Save the file into a specific folder.

Please Login or Register  to view this content.

The code works, but every so often the code hangs with a pop up box saying that it is saving the file, highlighting the correct location with the only option of removing the pop uop box via a 'Cancel' button.

When I do press 'Cancel' the code moves onto the next sheet, but does save the file, but it's a little time consuming to continue having to use the 'Cancel' button.

I didn't have a problem with this when running the code in a Windows XP and Excel 2003 environment, but I've now moved to windows 7 and Excel 2013.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may overcome this.

Many thanks and kind regards

Chris

Hi Chris,

Is it perhaps halting because the file name you're saving is already present?

Try adding the
Application.DisplayAlerts = False

instruction before the loop start.

Hi Chris,

My pleasure and thanks for the rep.

I had assumed of course that you did indeed want to overwrite the existing file if it exists. If you don't then you'll need to build in some code to move on to the next For instance and exit the loop early before the Save.

Originally Posted by hobbiton73

Hi Richard Buttery, thank you for coming back to me. If you're talking about replacing the newly created file with one of the same name which may exist in the 'Save' location, then the overwrite isn't an issue because any exisiting files in the 'Destination' folder are deleted via the 'Kill' lines I have in my code.

Kind Regards

Chris

Good point Chris. One which I had missed.