VBA Saving single sheet as CSV (not whole workbook)

Question

I appreciate there are lots of entries like save individual excel sheets as csv and Export each sheet to a separate csv file - But I want to save a single worksheet in a workbook.

My code in my xlsm file has a params and data sheet. I create a worksheet copy of the data with pasted values and then want to save it as csv. Currently my whole workbook changes name and becomes a csv.

How do I "save as csv" a single sheet in an Excel workbook?

Is there a Worksheet.SaveAs or do I have to move my data sheet to another workbook and save it that way?

CODE SAMPLE

' [Sample so some DIMs and parameters passed in left out]  Dim s1 as Worksheet Dim s2 as Worksheet  Set s1 = ThisWorkbook.Sheets(strSourceSheet) ' copy across s1.Range(s1.Cells(1, 1), s1.Cells(lastrow, lastcol)).Copy  ' Create new empty worksheet for holding values Set s2 = Worksheets.Add  s2.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats  ' save sheet s2.Activate strFullname = strPath & strFilename   ' >>> BIT THAT NEEDS FIXIN' s2.SaveAs Filename:=strFullname, _      FileFormat:=xlCSV, CreateBackup:=True  ' Can I do Worksheets.SaveAs? 

Using Windows 10 and Office 365

Answer 1

This code works fine for me.

Sub test()  Application.DisplayAlerts = False  ThisWorkbook.Sheets(strSourceSheet).Copy ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True ActiveWorkbook.Close  Application.DisplayAlerts = True  End Sub 

It's making a copy of the entire strSourceSheet sheet, which opens a new workbook, which we can then save as a .csv file, then it closes the newly saved .csv file, not messing up file name on your original file.

Answer 2

This is fairly generic

Sub WriteCSVs()  Dim mySheet As Worksheet Dim myPath As String  'Application.DisplayAlerts = False  For Each mySheet In ActiveWorkbook.Worksheets      myPath = "\\myserver\myfolder\"      ActiveWorkbook.Sheets(mySheet.Index).Copy     ActiveWorkbook.SaveAs Filename:=myPath & mySheet.Name, FileFormat:=xlCSV, CreateBackup:=True     ActiveWorkbook.Close  Next mySheet  'Application.DisplayAlerts = True  End Sub