save an excel file (with multiple linked sheets ) as values only

[Rodthemod]

Hi All

I wish to send excel files to other company staff - but save the file as values (so not showing all my formulae links etc )

Please help

Thanks in advance

Kind regards

David H

[Stewart Ross]

Hi. There is no simple in-built facility that allows you to save the contents of a workbook without its underlying formulas. Whilst it is possible to save Excel files in other file formats that save the values but not the formulas (e.g. CSV) this format does not support saving multiple worksheets, and you also lose the cell formatting applied to the worksheet concerned as well.

You could if you wanted to pursue this write VBA code to go through each worksheet using Copy/PasteSpecial/Values to overwrite the contents of each worksheet with values only. Not difficult, but it is destructive (the workbook would have to be based on a reusable template of some kind, as the formulas would disappear from all sheets on running the code).

All seems a fair bit of effort to guard against someone else seeing your formulas. In my experience this is rarely a necessary approach - unless the author of the sheet really has something to hide!!

-Stewart

[trevgobeep]

Select all. Copy the contents of the sheet.
Go to a blank sheet. Select all.

Right click in the upper left, unmarked block between A and 1.
Find paste special in the popup menu.
Look for the "Paste Values" options that work for you, regarding format.

[NeoPa]

If you put this code in a Standard Module you could call the procedure to do that for all your Worksheets :

Option Explicit
 
Public Sub AllValues()
    Dim wsVar As Worksheet, wsOrg As Worksheet
    Dim ranVar As Range
 
    Set ranVar = Selection
    For Each wsVar In ActiveWorkbook.Worksheets
        Call wsVar.Select
        With Range("A1", ActiveCell.SpecialCells(xlLastCell))
            Call .Copy
            Call .PasteSpecial(xlPasteValues)
        End With
    Next wsVar
    Application.CutCopyMode = False
    With ranVar
        Call .Worksheet.Select
        Call .Select
    End With
End Sub

[NeoPa]

I moved this question to the Excel forum for you BTW :-)