20 | 09 | 2017

Copy Sheet to new Workbook

This function copies a worksheet to a new workbook. Formulas will be removed and the sheet will be values only (including formats).

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
Login

Sign up now and upload your code to the website.

Help us to continue.....
Statistics
Articles View Hits
2353590
Latest Articles