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

Steve,

My approach would be to copy each worksheet to a new workbook. (This
prevents any code staying with the workbook.)

Just record code:
Creat a new workbook
Copy a worksheet in the template
Paste Special Values to the next workbook

Repeat for all sheets you want.

Than modify the code.

You can set the code to copy/paste simple ranges, or entire worksheets.

You can loop through all the worksheets (if you have multiple worksheets.


You can put the code in a standard module or you can put it into the
ThisWorkbook Module.
(This sample only copy all the cells on the active sheet and replaces the
formulas with values.)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cells.Copy
Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Let us know if this helps...

--
steveB

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

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

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

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

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