save excel values instead of formulas

[dave2]

Is there a way to save the values of the cells in excel instead of the formulas using the properties and methods? Or is there a way to set the tools->options->calculation to be manual in excel before saving it, using the application properties or methods? 

The problem I'm having is I open a template which autocalculates and retrieves a lot of values.  I then want to save those values down as a new worksheet, but it keeps saving the formulas and recalculating the sheet when I open the saved copy.  I have multiple worksheets so I can't just save the template as a delimited txt file.

Thanks in advance,

David

[Michael_Munroe]

Everything you need to do can be accomplished with activeX.  The only way to duplicate the open workbook as values only is sheet-by-sheet using Paste Special as values.  Instead of copying the entire workbook, just Add a new workbook, then select the entire sheet in the source book and paste special into the new workbook.  This will remove all of the formatting also.

You can expect the new workbook to have an unknown number of blank sheets, since it is settable in the options.  Use the sheets->count to avoid an error on the paste operation and insert a sheets->add operation.