I just came up with this:

Option Explicit

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 8/17/2006 by HP Authorized Customer
'

'
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

"Ryk" wrote:

What I am doing should be very easy, but alas I am trying this vb thing
for just the second application. I have a main sheet that has formulas
throughout it, and want to make a macro or something in VB to just make
a copy of the sheet into a new sheet, including formatting etc but no
formulas. I need send copies of the sheet out and the size is too
large with formulas included. I managed to find this next bit, and
from what little I know I think it would do the trick....

Sub ValsOnly()
Dim Rng As String, r As Range
Dim wb As Workbook
Set r = Sheets("Sheet1").UsedRange
Rng = r.Address
Workbooks.Add -4167
Set wb = ActiveWorkbook
With wb.ActiveSheet
..Range(Rng).Formula = r.Value
End With
End Sub

(forgot who posted this, but I think name was William, too much surfing
for answers hehe so if it helps and poster sees this, many thanks)

I have tried this in 2 blank workbooks, but cannot get it to work at
all, I think I need more instruction on what names I need include, ie
sheet1, or range name etc. Also do i need to have a second workbook
open? Named?

Thanks in advance.....

Ryk