Button to copy, save a sheet in a new file
 

hi
not sure if this is what you're after but you might be able to tweek it. i am an old time spreadsheet user. me and spread sheets go back to before windows.....before the mouse. i was a long time lotus user until my company decided to switch to excel. yuck. anyway this is a lotus feature i got use to called save range. in lotus it was a menu item. excel doesn't have it. so i had to write a macro in excel.
Sub mac1SaveRange()
'Macro written by FSt1 4/27/95
Dim cnt As Long
Dim cell As Range
On Error GoTo err1
MsgBox "You have selected range" & Selection.Address
If Selection.Cells.Count = 1 Then
If MsgBox("You have selected only one cell. Continue?????", vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
cnt = 0
For Each cell In Selection
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data in the selected range. Continue?!?!?!?!?", vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
'ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
err1:
MsgBox ("Need a range to save, diphead.")
Exit Sub
End Sub

i put some safety code to prevent me from trying to save an empty range or a single cell. you might want to change that. the code was designed to high light a range and save that range to a new workbook. i DO NOT RECOMMEND that you save a sheet with the safety code perticularly if using 07 or higher. you might be sitting there awhile while excel checks all the cells. you mentioned formulas... the code i wrote uses xlpasteall. you might want to change that to xlpastevalues. anyway look it over... tweek it as needed. i doubt that it will serve you as is. you mentioned a button. that is in the tool box. drop a button on the sheet and assign the finished macro to it. i sometimes use text boxes. six of one...half a dozen of the other. both work.

good luck
regards
FSt1