VBA Saving single sheet as CSV (not whole workbook)

23
votes

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

3
vbaexcel
If you use the Worksheet.Copy method, without setting a paste destination, Excel will open it as a new workbook, only containing that sheet. After Worksheet.Copy, you can simply use ActiveWorkbook, as the copied sheets workbook becomes the active workbook, and from there save it as a new CSV file. – Vulthil
Amazing - 2500 views and not a single vote... – micstr

3 Answers

30
votes

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.

4
votes

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
3
votes

You just need to save the workbook as a CSV file. Excel will pop up a dialog warning that you are saving to a single sheet, but you can suppress the warning with Application.DisplayAlerts = False.

Don't forget to put it back to true though.