CODEWITHSUNDEEP
excelvba
peter.domanico
peter.domanico

Reputation: 61

Export sheet as new Excel file (values only)

I found code in this discussion which has been extremely helpful for exporting Excel sheets as a new workbook. I've posted the version of the code that I currently use below.

As this code stands, it copies the content of the desired sheet to a new workbook, formulas and all.

Is it possible to modify this code to copy values only to this new workbook?

I appreciate any insight anyone can lend.

Sub ExportXLSX()

'exports desired sheet to new XLSX file

Dim MyPath As String

Dim MyFileName As String

Dim DateString As String

DateString = Format(Now(), "yyyy-mm-dd_hh_mm_ss_AM/PM")

MyFileName = DateString & "_" & "Whatever You Like"

If Not Right(MyFileName, 4) = ".xlsx" Then MyFileName = MyFileName & ".xlsx"

Sheets("Desired Sheet").Copy

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Where should we save this?"
    .AllowMultiSelect = False
    .InitialFileName = "" '<~~ The start folder path for the file picker.
    If .Show <> -1 Then GoTo NextCode
MyPath = .SelectedItems(1) & "\"

End With

NextCode:

With ActiveWorkbook
.SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
.Close False

End With

End Sub

Upvotes: 0

Views: 18880

Answers (1)

peter.domanico
peter.domanico

Reputation: 61

See revised NextCode section for solution:

NextCode:    

With ActiveWorkbook
        .ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value '<~~ converts contents of XLSX file to values only
        .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        .Close False
    End With

Upvotes: 1

Related Questions