ukdane
01-09-2009, 01:34 AM
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:
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
Thanks in advance.
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:
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
Thanks in advance.