I am working in Excel 2016 (16.0.4966.1000 32-bit)
I have a workbook that I update every month. Each month I make a copy of the previous month's workbook and update it with new data. It works for a time and then at some point every thing I do causes an "Excel has stopped working" message. Copying cells, saving the workbook, and on and on. Every single month this is happening. Performing the below steps seems to fix it until the next month. Step 9 never produces any compile errors. Since this is a known issue with the "temporary fix" outlined below, has it been fixed permanently in any updates? Perhaps in Excel 365 and not in 2016? Any insight is appreciated.
1.Open a new Excel workbook, not your crashing workbook
2.Go to the Developer Tab and click “Macro Security” in the Code section of the Ribbon
3.Click the bullet option entitled “Disable all macros with notification”
4.Go to the Trusted Locations tab on the left and check the “Disable all Trusted Locations” box at the bottom
5.Go to Trusted Documents and check the “Disable all Trusted Documents” box
6.Click OK and close the new workbook
7.Open your Excel file that was crashing
8.Do not click "Enable Macros" - instead go to the Developer Tab and open the Visual Basic Editor
9.In the VB Editor, save the project and then click Debug > Compile VBAProject
10.Correct any compile errors that occur
11.Save the VB project again and then save the Excel workbook
12.Revert any changes you made to Macro Security in steps 3-5
13.Close Excel, re-open the previously crashing workbook, and Enable Macros
