Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

@NathanClement was a bit faster. Yet, here is the complete code (slightly more elaborate):

Option ExplicitPublic Sub ExportWorksheetAndSaveAsCSV()Dim wbkExport As WorkbookDim shtToExport As WorksheetSet shtToExport = ThisWorkbook.Worksheets("Sheet1")     'Sheet to export as CSVSet wbkExport = Application.Workbooks.AddshtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)Application.DisplayAlerts = False                       'Possibly overwrite without askingwbkExport.SaveAs Filename:="C:\tmp\test.csv", FileFormat:=xlCSVApplication.DisplayAlerts = TruewbkExport.Close SaveChanges:=FalseEnd Sub

Almost what I wanted @Ralph, but here is the best answer, because it solves some problems:

1. it exports the current sheet, instead of just the hardcoded sheet named "Sheet1";
2. it exports to a file named as the current sheet
3. it respects the locale separation char.
4. You continue editing your xlsx file.

To solve these problems, and meet all my requirements, I've adapted the code from here. I've cleaned it a little to make it more readable.

Option ExplicitSub ExportAsCSV()     Dim MyFileName As String    Dim CurrentWB As Workbook, TempWB As Workbook         Set CurrentWB = ActiveWorkbook    ActiveWorkbook.ActiveSheet.UsedRange.Copy     Set TempWB = Application.Workbooks.Add(1)    With TempWB.Sheets(1).Range("A1")      .PasteSpecial xlPasteValues      .PasteSpecial xlPasteFormats    End With            Dim Change below to "- 4"  to become compatible with .xls files    MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"         Application.DisplayAlerts = False    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True    TempWB.Close SaveChanges:=False    Application.DisplayAlerts = TrueEnd Sub

There are still some small thing with the code above that you should notice:

1. .Close and DisplayAlerts=True should be in a finally clause, but I don't know how to do it in VBA
2. It works just if the current filename has 4 letters, like .xlsm. Wouldn't work in .xls excel old files. For file extensions of 3 chars, you must change the - 5 to - 4 when setting MyFileName in the code above.
3. As a collateral effect, your clipboard will be substituted with current sheet contents.

Edit: put Local:=True to save with my locale CSV delimiter.

As per my comment on @neves post, I slightly improved this by adding the xlPasteFormats as well as values part so dates go across as dates - I mostly save as CSV for bank statements, so needed dates.

Sub ExportAsCSV()    Dim MyFileName As String    Dim CurrentWB As Workbook, TempWB As Workbook    Set CurrentWB = ActiveWorkbook    ActiveWorkbook.ActiveSheet.UsedRange.Copy    Set TempWB = Application.Workbooks.Add(1)    With TempWB.Sheets(1).Range("A1")        .PasteSpecial xlPasteValues        .PasteSpecial xlPasteFormats    End With    'Dim Change below to "- 4"  to become compatible with .xls files    MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"    Application.DisplayAlerts = False    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True    TempWB.Close SaveChanges:=False    Application.DisplayAlerts = TrueEnd Sub