excel - 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.