0

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
Community
  • 1
  • 1
peter.domanico
  • 61
  • 1
  • 1
  • 9
  • have you searched for how to copy for values only as well (since you found the other helpful code)? there is a lot of questions like this on SO and the internet in general. – Scott Holtzman Jul 25 '16 at 16:40
  • @ScottHoltzman thanks for the response. I've found lots of useful code for pasting as values, but I haven't had any luck just copying for values. Since the code above doesn't use the paste command, I'm wondering how to proceed. I appreciate any ideas you may have. – peter.domanico Jul 26 '16 at 18:05
  • Just apply those methods to the used cells on sheet after the sheet itself is copied. Either `Copy | PasteSpecial xlValues` or `.Value = .Value` – Scott Holtzman Jul 27 '16 at 13:27
  • thanks Scott, I appreciate the direction. I've revised the NextCode section to accomplish this. Thanks again for the help! – peter.domanico Jul 27 '16 at 18:34
  • 1
    i rolled-back the edit you made since it would be confusing to others. Either show the new line in an answer or edit your original question to be clear that you found a solution and what it is. – Scott Holtzman Jul 27 '16 at 18:47

1 Answers1

1

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
peter.domanico
  • 61
  • 1
  • 1
  • 9