(Solved) – VBA Workbook Save As


I was hoping to save my new csv file sheet1 as “CSV123” but somehow the sheet name is always changed to filename after I save and close the file.

I am not sure why the .Sheets(1).Name = “CSV123” is registered when I use F8 to check but does not register after I close the workbook.

Sub Save_as_CSV()

Dim Newbook As Workbook
Dim filename As String
Dim answer As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

answer = InputBox("Please enter the file name for save", "CSV File Name")
If answer <> "" Then
    filename = Application.ThisWorkbook.Path & "" & answer & ".csv"
    Set Newbook = Workbooks.Add
    shcsv.Copy before:=Newbook.Sheets(1)
    With Newbook
        .SaveAs filename:=filename, FileFormat:=Excel.xlCSV
        .Sheets(1).Name = "CSV123"
    End With
Exit Sub
End If
 MsgBox "The CSV is exported to the same directory as this file."

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *