After macro runs I cannot save the workbook (excel keeps crashing!)

[Chris Macro]

I have a macro written that copies certain worksheets from my workbook and paste-values them into a new workbook. The macro runs fine, however after the macro has run and I go to save the newly created workbook, excel lets me select a file path but gets "frozen" when it tries to perform the save. I am wondering if I am missing anything in my code that would cause this to happen. I have one table named "DontExport" that just has names of worksheets I don't want copied over into the new workbook. Thanks for taking a look!

Code (running on Excel 2007):

<font face=Calibri><SPAN style="color:#00007F">Dim</SPAN> NewBook <SPAN style="color:#00007F">As</SPAN> Workbook<br><br><SPAN style="color:#00007F">Sub</SPAN> Export_NewWorkbook()<br><br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> WB_Created <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> SkipSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> SheetCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>WB_Created = <SPAN style="color:#00007F">False</SPAN><br>SkipSheet = <SPAN style="color:#00007F">False</SPAN><br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>Application.Calculation = xlCalculationManual<br><br><SPAN style="color:#007F00">'Create new Workbook</SPAN><br>    Application.SheetsInNewWorkbook = 1<br>    <SPAN style="color:#00007F">Set</SPAN> NewBook = Workbooks.Add<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        <br>        <SPAN style="color:#007F00">'Does this worksheet need to be skipped?</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets("Settings").ListObjects("DontExport").DataBodyRange.Cells<br>                <SPAN style="color:#00007F">If</SPAN> Cell.Value = sht.Name <SPAN style="color:#00007F">Then</SPAN> SkipSheet = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> Cell<br>        <br>        <SPAN style="color:#007F00">'Copy Worksheet into new workbook</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> SkipSheet = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                <br>                SheetCount = NewBook.Worksheets.Count<br>                sht.Copy after:=NewBook.Sheets(SheetCount)<br>                       <br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>            SkipSheet = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> sht<br><br><SPAN style="color:#007F00">'Delete Empty Sheet1</SPAN><br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        NewBook.Sheets(1).Delete<br>    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#007F00">'Paste Values for entire workbook</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> NewBook.Worksheets<br>        sht.Activate<br>        sht.Cells.Copy<br>        sht.Cells.PasteSpecial Paste:=xlPasteValues<br>           <br>        sht.Range("A1").Select<br>    <SPAN style="color:#00007F">Next</SPAN> sht<br><br><SPAN style="color:#007F00">'Select Sheet(1)</SPAN><br>    NewBook.Sheets(1).Activate<br><br><SPAN style="color:#007F00">'Completion Message</SPAN><br>    MsgBox "Export Complete"<br><br><br><SPAN style="color:#007F00">'Clear out Clipboard</SPAN><br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>Application.Calculation = xlCalculationAutomatic<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Chris Macro]

After a lot more testing I was able to figure out the root of the problem. I had a bunch of named ranges in my original workbook that were be linked when the new workbook was created. I guess this caused saving problems....I just had to throw in a loop to delete all the named ranges and presto! I can now Save!!!!