Exporting Worksheets as xlsm with Values not Formulas

[jpsimmon]

I am using an export code to take my worksheets to a destined folder in xlsm format. I am looking to adjust this formula so when the worksheets are exported it is a flat file that just contains the values in the cells NOT the formulas.

Sub CreateWorkbooks()Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object 
Dim strSavePath As String


On Error GoTo ErrorHandler


Application.ScreenUpdating = False 


strSavePath = "S:\Test\" 


Set wbSource = ActiveWorkbook


For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 
Next


Application.ScreenUpdating = True


Exit Sub


ErrorHandler: 
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub

Thanks for the help

 

[mole999]

you might want to consider copy and paste your data as values, just be sure you don't overwrite your live file

 

[jpsimmon]

You have any lines that would do this that I can incorporate?

 

[mole999]

    Range("A1:Z1000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[jpsimmon]

It still transfers the formulas to the exports, not just the values

Sub CreateWorkbooks()Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False

strSavePath = "S:\Test\"

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
    Range("G1:W645").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
sht.Copy

Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
Next

Application.ScreenUpdating = True

Exit Sub

ErrorHandler:
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub

 

[mole999]

before sht.copy, break the code and see whats stored in the formula cells