Solved: Copying worksheet formula results to a new workbook.

[ukdane]

A colleague of mine wants to create a macro, so that when they press a button, the current worksheet generates and saves a new workbook.
The new workbook must contain the contents of the sheet that was active when they pressed the button that activated the macro.

However the contents of the new workbook must only contain the results of any formula from the old worksheet, and not the formula itself.

(Using Activesheet.copy copies the formula).

Here's the code I have so far, although I think it's a bit messy, as it has been adapted from some other code, and it doesn't allow the user to choose the destination and filename to save the new workbook to:

[VBA]Sub Copyworksheet()
'Variable declaration
Dim oApp As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to a file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = ActiveSheet.Name & ".xls"
On Error Resume Next
Kill "C:\Documents and Settings\All Users\Desktop\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\Documents and Settings\All Users\Desktop\" & FileName

Application.ScreenUpdating = True
Set oApp = Nothing

End Sub[/VBA]

Thanks in advance.

[ukdane]

If refined the code I had something rotten:

[VBA]Sub Copyworksheet()
ActiveSheet.Copy

ChDir ThisWorkbook.Path

Application.Dialogs(xlDialogSaveAs).Show

If Not ActiveWorkbook.Name = ThisWorkbook.Name _
Then ActiveWorkbook.Close False

End Sub
[/VBA]

Which is much neater. However the Copied sheet still includes all formula, and not the results of the formula.

Any ideas?

Cheers

[georgiboy]

You may want to do this to the new workbook to lose the formulae.

[VBA] Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False[/VBA]

[georgiboy]

[VBA]Sub Copyworksheet()
ActiveSheet.Copy

ChDir ThisWorkbook.Path

Application.Dialogs(xlDialogSaveAs).Show

ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

If Not ActiveWorkbook.Name = ThisWorkbook.Name _
Then ActiveWorkbook.Close True

End Sub[/VBA]

[ukdane]

Great thanks