Excel shuts down after I copy multiple worksheets, then save.

Wret

New Member
Joined
Jun 17, 2008
Messages
12
I have a workbook with 78 identical, protected worksheets and a summary page with links to specific cells on each page. When I need to make a change, it is much simpler to delete all sheets but one, make the change once, make 77 copies, rename them and reconstruct the summary page. It never fails that Excel will close when I try to save the workbook. I combat this by copying in smaller groups and saving after every change. This helps but I can still plan on it shutting down 3 or 4 times before I finish.

Anyone know why this happens or what I can do to prevent it?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Wret, welcome to MrExcel

I would like to inform you that the cause of the problem that you are currently experiencing may contain a lot of possibilities.

The best is to do a search on Google.com. Besides, you need to check if Microsoft has listed a resolution which could be similar to the problem that you are experiencing. You can do this by searching for relevant KB Articles on http://support.microsoft.com. Choose an advanced search for your problem on the website.

If you are unable to find the resolution, then come back and let us know. We would do our best to help you out. Just to begin with, we would require some information from your end:

what is the file size of your workbook ? What do you have in this workbook ? Are there any macros in this workbook ? Which version of Excel do you use on your computer ? What is the level of security in your workbook? Is your workbook protected ? Does it throw an error message at the time you save your workbook ? If yes, then exactly what error message do you get ? Are you on a network ? Do you experience the same problem with all the other workbooks or this particular workbook ? What other ways have you performed in order to save the workbook ?
 
Upvote 0
I had a similar problem which was causing me real grief. It seems there is a bug in Excel that causes it. The workaround is to create a template file with your sheet in and use the add method:

ThisWorkbook.Sheets.Add Type:=TempPath & "\MyTemplate.xlt"

In this case TempPath is the path to my MyTemplate.

To create the template, copy wour worksheet to a new file and save as type Template (*.xlt)

HTH
 
Upvote 0
Thanks for the feedback, I'll follow up on your suggestions.

Atually I have the same problem on two entirely different workbooks one is about 3600kb and the other is about 7000kb. Neither has any macros or external references. Both have a lot of formulas and conditional formats. I never have a problem working in the workbooks or copying low number of sheets but it seems when I copy a larger number, say 8-10, excel crashes when saving.

I am using version 2003 11.8169.8172 SP3

I get the basic "Microsoft Excel has experienced a problem and needs to close" window.

I should add that until recently this only occured during saving but while testing just now, and once recently, it occured while copying the worksheets.
 
Upvote 0
Both have a lot of formulas and conditional formats. I never have a problem working in the workbooks or copying low number of sheets but it seems when I copy a larger number, say 8-10, excel crashes when saving

Exactly. I reckon, this is all due to the calculation problems Excel experiences on your worksheets with large volume of formulae and conditional formats.

Check out these links:
1) http://www.decisionmodels.com/calcsecretsf.htm
2) http://www.decisionmodels.com/calcsecretsd.htm

For instance, try turning off the Calculation, i.e, turning the calculation to "Manual" - Tools --> Options --> Calculation --> Manual --> Remove checkmark for "Re-calculate before save" and then try saving your workbook. You will see Excel will not crash this time.
 
Upvote 0
he unchecked "calculate when saving" will help me restore my summary page but it doesn't prevent the crash. I've been running with calculation on manual. It typically finishes calculating, then crashes during the save.

Thanks for the tip. This will save me some time even if I don't find a solution to the problem.
 
Upvote 0
hi again,

did you try reinstalling / repairing Excel or Microsoft Excel ?

Also, at the time you are saving the file, try not to open any other application on your computer and also do not click or do anything unless it saves the file. Multitasking will lead to the memory being allocated to other resources on the computer which is a possible cause for the crash too.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top