By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,583 Members | 617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,583 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Nov 13 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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
Nov 13 '08 #2

P: 1
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.
Apr 25 '13 #3

NeoPa
Expert Mod 15k+
P: 31,419
If you put this code in a Standard Module you could call the procedure to do that for all your Worksheets :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub AllValues()
  4.     Dim wsVar As Worksheet, wsOrg As Worksheet
  5.     Dim ranVar As Range
  6.  
  7.     Set ranVar = Selection
  8.     For Each wsVar In ActiveWorkbook.Worksheets
  9.         Call wsVar.Select
  10.         With Range("A1", ActiveCell.SpecialCells(xlLastCell))
  11.             Call .Copy
  12.             Call .PasteSpecial(xlPasteValues)
  13.         End With
  14.     Next wsVar
  15.     Application.CutCopyMode = False
  16.     With ranVar
  17.         Call .Worksheet.Select
  18.         Call .Select
  19.     End With
  20. End Sub
Apr 25 '13 #4

NeoPa
Expert Mod 15k+
P: 31,419
I moved this question to the Excel forum for you BTW :-)
Apr 25 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.