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

Question

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like this:

Sub SaveAsCSV()     ActiveWorkbook.SaveAs FileFormat:=clCSV, CreateBackup:=False End Sub 

This is a great answer, but I want to do an export instead of Save As. When the SaveAs is executed it causes me two annoyances:

• My current working file becomes a CSV file. I'd like to continue working in my original .xlsm file, but to export the contents of the current worksheet to a CSV file with the same name.
• A dialog appears asking me confirm that I'd like to rewrite the CSV file.

Is it possible to just export the current worksheet as a file, but to continue working in my original file?

Answer 1

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

Option Explicit  Public Sub ExportWorksheetAndSaveAsCSV()  Dim wbkExport As Workbook Dim shtToExport As Worksheet  Set shtToExport = ThisWorkbook.Worksheets("Sheet1")     'Sheet to export as CSV Set wbkExport = Application.Workbooks.Add shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count) Application.DisplayAlerts = False                       'Possibly overwrite without asking wbkExport.SaveAs Filename:="C:\tmp\test.csv", FileFormat:=xlCSV Application.DisplayAlerts = True wbkExport.Close SaveChanges:=False  End Sub