VBA Macro to Export Data from Excel Spreadsheet to CSV

Solution #1:
    Worksheet.SaveAs filename:=CSVFileName, FileFormat:=xlCSV
cons: both excel file name and worksheet name are also renamed to CSVFileName

Solution #2:
    Dim oldName As String
    Dim oldPath As String
    Dim oldFormat As Integer
    Dim oldSheetName As String
    Application.DisplayAlerts = False  'avoid safetey alert
    With ActiveWorkbook
        oldName = .Name
        oldPath = .Path
        oldFormat = .FileFormat
        oldSheetName = LoaderSheet.Name
        LoaderSheet.SaveAs filename:=CSVFileName, FileFormat:=xlCSV
        LoaderSheet.Name = oldSheetName
        .SaveAs filename:=oldPath + "\" + oldName, FileFormat:=oldFormat
    End With
    Application.DisplayAlerts = True
Cons: working but clumsy and takes longer to execute

Solution #3:
    LoaderSheet.Copy
    With ActiveWorkbook
        .SaveAs filename:=CSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close False
    End With
Cons: Can visually see a new excel workbook is created and then disappear
          Also potential problem with Accounting data type columns, it might export data like " (13,534,971.48)"

Solution #4:
    LoaderSheet.UsedRange.Copy

    Dim wb2 As Workbook
    Set wb2 = Application.Workbooks.Add

    wb2.Sheets(1).Range("A1").PasteSpecial xlPasteValues

    Application.DisplayAlerts = False
    wb2.SaveAs filename:=CSVFileName, FileFormat:=xlCSV
    wb2.Close True
    Application.DisplayAlerts = True
Cons: Can visually see a new excel workbook is created and then disappear