Hi Steve,

That is a good point, it depends on what effect the OP is after, does the OP
want the code to work on distributed files? - I have some that do and some
that don't. In any case, between the two of us we've covered both bases...

"STEVE BELL" wrote:

The only problem I have with copying to the template file and doing a
Save-As is that the code stays with the workbook unless you use code to
remove all the code.

Copying to a new workbook creates a code-free workbook.

--
steveB

Remove "AYN" from email to respond
"quartz" wrote in message
...
One way:

Copy the following sub into the "ThisWorkbook" module (NOTE: edit the
sheet
name appropriately):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call ReplaceFormulasWithValues("Sheet1") '<change to your sheet name
End Sub

Then copy the following function into a normal module:

Public Function ReplaceFormulasWithValues(argSheetName As String)
'REPLACE SHEET CONTENTS WITH VALUES
Dim sSelection As String
Sheets(argSheetName).Activate
sSelection = Selection.Address
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range(sSelection).Select
End Function

HTH/

"Steve B" wrote:

I have a template which draws values from formulas that reference other
workbooks. Is there a way, on save, to convert all formulas to values so
the
new workbook is a 'snapshot' that can be distributed without needing the
reference to the linked files?

TIA, Steve