|
Tutorials
|
|
This function copies a worksheet to a new workbook. Formulas will be removed and the sheet will be values only (including formats).
Show/Hidden vbnet code  Option Explicit
Function ExportCalculation(ByVal Sheet2Export As Worksheet)
'*************************************
'**** Code from VisibleVisual.com ****
'*************************************
Application.ScreenUpdating = False
On Error GoTo ErrHandler
Sheet2Export.Copy
Dim Dstfile As Variant
Dim Vreturn As String
Dstfile = Application.GetSaveAsFilename("EXPORTED FILE" & ".xls", "Excel files (*.xls), *.xls")
If Fileexists(Dstfile) = True Then
Vreturn = MsgBox("Filexists do you wish to overwrite?", vbYesNo, "Microsoft Excel Save File")
If Vreturn <> 6 Then GoTo endif1
End If
ActiveWorkbook.SaveCopyAs Dstfile 'Save file
ActiveWorkbook.Close SaveChanges:=False
endif1:
End With
Application.ScreenUpdating = True
Exit Function
ErrHandler:
Application.ScreenUpdating = True
MsgBox Err.Description
End Function
Public Function Fileexists(Fname) As Boolean
If Left(Fname, 4) = " " Then
Fileexists = False
Else
If Dir(Fname) <> "" Then
Fileexists = True
Else
Fileexists = False
End If
End If
End Function
Sub TestFunction()
ExportCalculation ActiveSheet
End Sub
|
