How to use Excel .SaveAs but retain original sheet name

Question

I am working with Excel 2010, and have a userform which will support various processing options (i.e. sort by predefined ranges, get statistics, and the dreaded 'export' (SaveAs). I want to allow the user to export one of the sheets as either CSV or XLSX.

The issue is when I use the SaveAs to save as a CSV, it renames the sheet to the filename I selected (minus the extension). I have searched for hours and have not found any place that provides a solution. I did find a Stack 5+ year old post, but it didn't have a solution (see How to stop renaming of excelsheets after running the save macro)

Any help would be appreciated! Thanks!

Here is the code:

Dim ws              As Excel.Worksheet
Dim strSaveName     As String
Dim strThisName     As String

strThisName = ThisWorkbook.Path & ThisWorkbook.Name
strSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Text Files (*.csv), *.csv")

Set ws = Worksheets("Export")
ws.SaveAs Filename:=strSaveName, FileFormat:=xlCSV

'I do the following TO UNDO THE RENAME <GROAN> (for now just saving as 0.csv)
Sheets("0").Name = "Export"  

' Then the following is to regain my original filename since I will continue working...
ws.SaveAs Filename:=strThisName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Answer 1

Use ws.Copy with no args beforehand then save the new workbook

e.g. adapt your code to be like:

    Dim ws              As Excel.Worksheet
    Dim strSaveName     As String

    strSaveName = Application.GetSaveAsFilename( _
        fileFilter:="Text Files (*.csv), *.csv")

    Set ws = Worksheets("Export")

    'Copy the ws to a new workbook
    ws.Copy
    'With the new wb:
    With Workbooks(Workbooks.Count)
        'Save and close the new workbook
        .SaveAs Filename:=strSaveName, FileFormat:=xlCSV
        .Close False
    End With

p.s. I assume you have code to handle clicking Cancel on GetSaveAsFilename and just removed it for clarity in the question ;)