VBA copy formulas to values and change filename

[vilamall]

Hi guys!

I want to export a spreadsheet to a folder located on the desktop.

I have two problems:
I want to convert formulas to cell values in the exported sheet (formulas that result in blank cells have to remain blank in the exported sheet).
I also want to change the name of the exported sheet to: Spreadsheet name & "_" & File name & ".xlsx".

Thank you.

Sub ExportWorksheets()

    Dim worksheet_list As Variant, worksheet_name As Variant
    Dim new_workbook As Workbook
    Dim saved_folder As String

    worksheet_list = Array("Export")
    
    '// makes sure you close the path with a back slash \
    saved_folder = Environ("userprofile") & "\Desktop\Export folder\"
    
    
    For Each worksheet_name In worksheet_list
    
        On Error Resume Next
        ' Opens a new Excel wokrobook
        Set new_workbook = Workbooks.Add
        
        ThisWorkbook.Worksheets(worksheet_name).Copy new_workbook.Worksheets(1)
        
        new_workbook.SaveAs saved_folder & worksheet_name & ".xlsx", 51
        new_workbook.Close False
        
    Next worksheet_name
    
    MsgBox "Export complete.", vbInformation
    
End Sub

 

[rlv01]

Not sure I understand how you want to rename the file.

Sub ExportWorksheets()

    Dim worksheet_list As Variant, worksheet_name As Variant
    Dim new_workbook As Workbook
    Dim saved_folder As String
    Dim WS As Worksheet

    worksheet_list = Array("Export")

    '// makes sure you close the path with a back slash \
    saved_folder = Environ("userprofile") & "\Desktop\Export folder\"

    For Each worksheet_name In worksheet_list
        Set WS = Nothing
        Set new_workbook = Nothing

        ' Opens a new Excel wokrobook
        On Error Resume Next
        Set new_workbook = Workbooks.Add
        Set WS = ThisWorkbook.Worksheets(worksheet_name)
        On Error GoTo 0

        If Not new_workbook Is Nothing And Not WS Is Nothing Then
            WS.Copy new_workbook.Worksheets(1)
            WS.UsedRange.Copy
            new_workbook.Worksheets(worksheet_name).Range("A1").PasteSpecial (xlPasteValues)

            Application.DisplayAlerts = False
            new_workbook.SaveAs saved_folder & worksheet_name & ".xlsx", 51
            Application.DisplayAlerts = True
            new_workbook.Close False
        End If
    Next worksheet_name

    MsgBox "Export complete.", vbInformation
End Sub