Quartz,

Thanks for the kind words!

As always it is not a matter of if, but a matter of what you want...

The hardest issue I have is getting my clients to be very specific about
what they want - And never ask If...

--
steveB

Remove "AYN" from email to respond
"quartz" wrote in message
...
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