Changing formulas to values-CodePudding

I have this code that exports a worksheet to the desktop. I want to change the formulas to values but only in the exported file, but I don't know how to do it.

Thanks.

Sub ExportWorksheets()

    Dim worksheet_list As Variant, worksheet_name As Variant
    Dim new_workbook As Workbook
    Dim saved_folder As String
    Dim File_name As String
    Dim New_File_Name As String
    
    worksheet_list = Array("Sheet_02")
    
    
    '// makes sure you close the path with a back slash \
    saved_folder = Environ("userprofile") & "\Desktop\"
    
    For Each worksheet_name In worksheet_list
    
        On Error Resume Next
        ' Opens a new Excel wokrobook
        Set new_workbook = Workbooks.Add
        
        File_name = ThisWorkbook.Name
        
        File_name_02 = Replace(File_name, ".xlsm", "")
        
        New_File_Name = worksheet_name & "_" & File_name_02 & ".xlsx"

        
        ThisWorkbook.Worksheets(worksheet_name).Copy new_workbook.Worksheets(1)
        
        
        new_workbook.SaveAs saved_folder & New_File_Name, 51
        new_workbook.Close False
        
    Next worksheet_name
    
 
    MsgBox "Export completed. " & New_File_Name, vbInformation
    
End Sub

CodePudding user response：

Please, try the next updated code. You do not need to previously create a new workbook, and .Value = .Value does what you need:

Sub ExportWorksheets()
    Dim worksheet_list As Variant, worksheet_name As Variant
    Dim saved_folder As String, File_name As String, New_File_Name As String
    
    worksheet_list = Array("Sheet_02")
    
    '// makes sure you close the path with a back slash \
    saved_folder = Environ("userprofile") & "\Desktop\"
    
    For Each worksheet_name In worksheet_list
        File_name = ThisWorkbook.name
        File_name_02 = Replace(File_name, ".xlsm", "")
        New_File_Name = worksheet_name & "_" & File_name_02 & ".xlsx"

        ThisWorkbook.Worksheets(worksheet_name).Copy 'it automatically create a new workbook with the content of the respective sheet
        With ActiveWorkbook.Sheets(1).UsedRange
            .value2 = .value2 'value2 is faster and may be used since the range has the same format...
        End With
        
        ActiveWorkbook.saveas saved_folder & New_File_Name, 51
        ActiveWorkbook.Close False
    Next worksheet_name
    
    MsgBox "Export completed. " & New_File_Name, vbInformation
End Sub