Hi Leslie,

Here's a routine that I've been using for quite a few years that will copy a
single sheet from a workbook, values only, and then prompt the user (i.e.
you!) to save the static, values only worksheet as a new workbook instance.
In that way, you can freely distribute the new static, values only workbook
and not have to worry about any linking back to source workbook issues.

Sub RangeValue()
WSToExport = Application.InputBox(Prompt:="Enter Name of Worksheet to
Export:", _
Title:="Export Worksheet (Range Valued)", Default:=ActiveSheet.Name,
Type:=2)
' What if User clicks 'Cancel'?
If VarType(WSToExport) = vbBoolean And WSToExport = False Then Exit Sub
OrigWbName = Worksheets(WSToExport).Parent.Name
With Workbooks(OrigWbName).Worksheets(WSToExport)
.Copy
.UsedRange.Copy
End With
ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
ExportFileName =
Application.GetSaveAsFilename(InitialFileName:=WST oExport,
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
If ExportFileName < False Then ActiveWorkbook.SaveAs
FileName:=ExportFileName, FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
End Sub

Of course you can wrap the relevant lines of code above in a For Each ...
Next block to loop through each worksheet of the workbook in question. Some
further tweaks probably required in order to fit your specific purpose, but I
hope that gives you the general idea and a starting point.

HTH, Sean.

"Leslie" wrote:

Thank you I will give it a shot. What if I have multiple sheets in a
workbook, is there a macro that will copy and save the whole workbook. Also,
when I do this macro is it saving the formulas or the values. If it creates a
new workbook with the formulas I still have the problem of no one being able
to read the document because they don't have the source document. Thanks.

" wrote:

Hey leslie,

If you want to copy a sheet from one workbook to another workbook and
save the destination workbook, you can easily use the macro recorder.
Think this is how it should look like.

Sheets("Sheet1").Select
Sheets("Sheet1").Copy
Befo=Workbooks("destination.xls").Sheets(1)
wkbk.Close SaveChanges:=True
MsgBox ("DONE!")

Greetz