save or print a Excel file as a formula-free Excel file

[lalbatros]

Hello,

I have been using the pdf format since long, to send reports based on multiple sheets from a workbook.
Now, the XL2010 built-in pdf printing feature in quite nice.
I have also given a try to the xps format.

However, most people receiving my reports would preffer to receive an Excel file.
An Excel file is far more convenient for copying data when they need to.
Unfortunately, I could not send the full Excel file to my collegues.
This is because of the complex logic behind this workbook: some 100 pages, connections to about 10 big tables on an sql server, 25 Mbytes, some user-defines functions, and a VSTO package supporting it, many pivot tables.

How would you suggest me to proceed?
Would there be a simple way to "print" a subset of the sheets to another Excel file, keeping the data and the charts and maybe the (local) hyperlinks, but removing macros, formulas, connections.
I would of course prefer a simple solution, almost as simple a printing to a pdf file.

Thanks,

Michel

 

[niceguy_jay]

try this code...your new workbook will be saved under the same folder with the name as new.xls

Sub save_macro_free()
Dim neo As Workbook
Save_Filename = ActiveWorkbook.path & "\" & "new" & ".xls"
With neo
        Dim sh As Object
        ThisWorkbook.Activate
        For Each sh In ThisWorkbook.Sheets
            sh.Select False
        Next sh
        ThisWorkbook.Windows(1).SelectedSheets.Copy
        Set neo = ActiveWorkbook
        neo.SaveAs Filename:=Save_Filename, FileFormat:=xlExcel8, CreateBackup:=False
        neo.Close
End With
End Sub

 

[lalbatros]

Thanks!

But would it be possible to have all formulas removed, as well as all links?
Roughly, I would like the xl-printout to be nearly like a pdf except that it would keep the spreadsheet structure.